﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO
Imports System.Configuration

Public Class uMESCheckMainBusiness
    Implements uMESCheckMainIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

#Region "获取跟踪卡基本信息"
    Function GetContainerInfo(ByVal strContainerName As String) As DataTable

        Dim strSql As String = "SELECT c.productid  FROM Container c  WHERE c.containername='" & strContainerName & "'"

        Dim productDT As DataTable = dal.GetContainerInfo(strSql)

        'Dim strQuery As String = "SELECT c.TogetherFlag FROM Container c WHERE c.containername = '" & strContainerName & "' AND c.productid='" & productDT.Rows(0)("productid").ToString().Trim() & "'" & vbCrLf


        Dim strQuery As String = "SELECT c.togetherflag,pb.productname,c.productid  FROM Container c  " & vbCrLf & _
                                 "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                                 "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                                 "WHERE c.containername = '" & strContainerName & "' AND c.productid='" & productDT.Rows(0)("productid").ToString().Trim() & "'"

        Dim result As New uMESProcessResult
        Dim DT As DataTable = dal.GetContainerInfo(strQuery)
        Return DT
    End Function
#End Region

    ''' <summary>
    ''' 获取批次当前状态信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerCurrentInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetContainerCurrentInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strTogetherFlag As String = ""
            Dim strContainerName As String = ""

            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString
                If strContainerName.Trim() <> "" Then
                    Dim r As DataTable = GetContainerInfo(strContainerName)
                    If r.Rows(0).IsNull("TogetherFlag") = False Then
                        strTogetherFlag = r.Rows(0)("TogetherFlag").ToString
                    End If
                End If
            End If

            Dim strSContainerName As String = ""
            If QueryCondition.Keys.Contains("SearchContainerName") = True Then
                strSContainerName = QueryCondition("SearchContainerName").ToString()
            End If
            Dim strProductName As String = ""
            If QueryCondition.Keys.Contains("ProductName") = True Then
                strProductName = QueryCondition("ProductName").ToString()
            End If
            Dim strSpecName As String = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If
            Dim strFactoryName As String = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If
            Dim strWorkCenterName As String = ""
            If QueryCondition.Keys.Contains("WorkCenterName") = True Then
                strWorkCenterName = QueryCondition("WorkCenterName").ToString()
            End If
            Dim strCheckSpecKey As String = ConfigurationSettings.AppSettings("CheckSpecKey")
            Dim strCheckOperName As String = ConfigurationSettings.AppSettings("CheckRole")

            Dim strKeys As String() = strCheckSpecKey.Split(",")

            ''"AND  NVL(cr.Qty, 0) > 0" & vbCrLf

            Dim strSQL As String = "SELECT * FROM" & vbCrLf & _
                                   "(SELECT c.togetherflag,c.Containername, pb.Productname, p.Productrevision, sb.Specname,s.Specrevision, c.Qty, NVL(cr.Qty,0) AS ReportQty," & vbCrLf & _
                                   "(NVL(cr.Qty, 0) - NVL(ce.Eligibilityqty, 0) - NVL(ce.Nonsenseqty,0)) AS NeedCheckQty, NVL(ce.Nonsenseqty,0) AS Nonsenseqty, " & vbCrLf & _
                                   "NVL(ce.Eligibilityqty,0) AS EligibleQty, p.Description AS ProductDesc," & vbCrLf & _
                   "c.Containerid, s.Specid, wfs.Islaststep,  c.Childcount,cr.reporttype2," & vbCrLf & _
                   "(CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                   "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                   "cr.mutualcheckemployeeid,op.operationname " & vbCrLf & _
                   "FROM Container c" & vbCrLf & _
                   "INNER JOIN Product p ON c.Productid = p.Productid" & vbCrLf & _
                   "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid" & vbCrLf & _
                   "INNER JOIN Currentstatus cs ON cs.Currentstatusid = c.Currentstatusid" & vbCrLf & _
                   "INNER JOIN Spec s ON cs.Specid = s.Specid" & vbCrLf & _
                   "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                   "INNER JOIN operation op ON op.operationid = s.operationid" & vbCrLf & _
                   "LEFT JOIN Factory f ON f.Factoryid = cs.Factoryid" & vbCrLf & _
                   "LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid" & vbCrLf & _
                   "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid" & vbCrLf & _
                   "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid" & vbCrLf & _
                   "INNER JOIN Cdodefinition cdo ON cdo.Cdodefid = w.Cdotypeid" & vbCrLf & _
                   "LEFT JOIN" & vbCrLf & _
                   "(SELECT wcd.Containerid, wcd.Specid,NVL(SUM(wrh.Qty), 0) AS Qty,(CASE WHEN  wrh.reporttype2=0 THEN '是' ELSE '否' END ) AS reporttype2,wrh.mutualcheckemployeeid " & vbCrLf & _
                   "FROM WorkCenterDispatching wcd" & vbCrLf & _
                   "INNER JOIN Workingreporthistory wrh ON wcd.WorkCenterDispatchingid = wrh.WorkCenterDispatchingid" & vbCrLf & _
                   "INNER JOIN Historymainline hml ON hml.Historymainlineid = wrh.Historymainlineid" & vbCrLf & _
                   "WHERE hml.reversalstatus = 1 GROUP BY wcd.Containerid, wcd.Specid,wrh.reporttype2,wrh.mutualcheckemployeeid " & vbCrLf & _
                   ") cr ON cr.Containerid = c.Containerid AND cs.Specid = cr.Specid" & vbCrLf & _
                   "LEFT JOIN" & vbCrLf & _
                   "(SELECT wcd.Containerid, wcd.Specid,NVL(SUM(cch.Eligibilityqty), 0) AS Eligibilityqty,NVL(SUM(cch.nonsenseqty), 0) AS Nonsenseqty" & vbCrLf & _
                   "FROM WorkCenterDispatching wcd" & vbCrLf & _
                   "INNER JOIN Workingreporthistory wrh ON wcd.WorkCenterDispatchingid = wrh.WorkCenterDispatchingid" & vbCrLf & _
                   "INNER JOIN Conventioncheckhistory cch ON wrh.Workingreporthistoryid = cch.Workingreportid" & vbCrLf & _
                   "INNER JOIN Historymainline hml ON hml.Historymainlineid = cch.Historymainlineid" & vbCrLf & _
                   "WHERE hml.reversalstatus = 1 GROUP BY wcd.Containerid, wcd.Specid" & vbCrLf & _
                   ") ce ON ce.Containerid = c.Containerid AND cs.Specid = ce.Specid" & vbCrLf & _
                   "WHERE c.Status =1 AND c.Qty >0 AND c.Parentcontainerid IS NULL AND cdo.Cdoname = 'MachineMfgWorkFlow' " & vbCrLf


            If strTogetherFlag.Trim() <> "" Then
                strSQL += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
            Else
                If strContainerName <> "" Then
                    strSQL += "AND c.ContainerName = '" & strContainerName & "'" & vbCrLf
                Else
                    If strSContainerName.Trim() <> "" Then
                        strSQL += "AND c.ContainerName like '%" & strSContainerName & "%'" & vbCrLf
                    End If
                End If
                If strProductName <> "" Then
                    strSQL += "AND pb.Productname LIKE '%" & strProductName & "%'" & vbCrLf
                End If
                If strSpecName <> "" Then
                    strSQL += "AND sb.Specname LIKE '%" & strSpecName & "%'" & vbCrLf
                End If
                If strFactoryName <> "" Then
                    strSQL += "AND f.Factoryname = '" & strFactoryName & "'" & vbCrLf
                End If
                If strWorkCenterName <> "" Then
                    strSQL += "AND wc.workcentername = '" & strWorkCenterName & "'" & vbCrLf
                End If

            End If

            strSQL += "AND c.containerid NOT IN" & vbCrLf & _
                                "(" & vbCrLf & _
                                "SELECT hml.containerid" & vbCrLf & _
                                "FROM historymainline hml" & vbCrLf & _
                                "LEFT JOIN cdodefinition cd ON cd.cdodefid = hml.txntype" & vbCrLf & _
                                "LEFT JOIN speclastcheckhistory slc ON slc.historymainlineid = hml.historymainlineid" & vbCrLf & _
                                "WHERE cd.cdoname = 'SpecLastCheck' AND slc.checktype = 2 AND hml.reversalstatus = 1" & vbCrLf & _
                                ")" & vbCrLf
            strSQL = strSQL & ")  WHERE 1=1 "

            Dim strEmployID As String = ""
            If QueryCondition.Keys.Contains("employID") = True Then
                strEmployID = QueryCondition("employID").ToString()
                strSQL = strSQL & " AND (mutualcheckemployeeid='" & strEmployID & "'  OR mutualcheckemployeeid IS NULL) "
            End If
            strSQL = strSQL & " AND （ReportQty>0 OR operationname='" & strCheckOperName & "'） ORDER BY Containername, Specname "

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL
            Dim result As New uMESProcessResult

            oResult = dal.RunProcedureForPage(paraPage)
            Dim dt As New DataTable
            dt = oResult.DBTable.Copy
            If dt.Rows.Count > 0 Then
                oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dt)
            End If
            result.Result = True

            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

#Region "获取工序检验信息"
    'Public Function GetContainerCurrentInfoNew(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetContainerCurrentInfoNew
    '    Dim oResult As New uMESProcessResult

    '    Dim strTogetherFlag As String = ""
    '    Dim strContainerName As String = ""
    '    Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

    '    Dim strProductName As String = ""
    '    If QueryCondition.Keys.Contains("ProductName") = True Then
    '        strProductName = QueryCondition("ProductName").ToString()
    '    End If

    '    If strProductName.Trim() = "" Then
    '        If QueryCondition.Keys.Contains("ContainerName") = True Then
    '            strContainerName = commBusiness.ReplaceContainerName(QueryCondition("ContainerName").ToString.Trim())

    '            If strContainerName.Trim() <> "" Then
    '                Dim r As DataTable = GetContainerInfo(strContainerName)
    '                If r.Rows(0).IsNull("TogetherFlag") = False Then
    '                    strTogetherFlag = r.Rows(0)("TogetherFlag").ToString
    '                    strProductName = r.Rows(0)("productname").ToString
    '                End If
    '            End If
    '        End If
    '    End If


    '    Dim strSContainerName As String = ""
    '    If QueryCondition.Keys.Contains("SearchContainerName") = True Then
    '        strSContainerName = QueryCondition("SearchContainerName").ToString()
    '    End If

    '    Dim strSpecName As String = ""
    '    If QueryCondition.Keys.Contains("SpecName") = True Then
    '        strSpecName = QueryCondition("SpecName").ToString()
    '    End If
    '    Dim strFactoryName As String = ""
    '    If QueryCondition.Keys.Contains("FactoryName") = True Then
    '        strFactoryName = QueryCondition("FactoryName").ToString()
    '    End If
    '    Dim strWorkCenterName As String = ""
    '    If QueryCondition.Keys.Contains("WorkCenterName") = True Then
    '        strWorkCenterName = QueryCondition("WorkCenterName").ToString()
    '    End If

    '    'Dim strCheck1SpecKey As String = ConfigurationSettings.AppSettings("CheckSpecKey") '工序检工种关键字

    '    'Dim strCheck2SpecKey As String = ConfigurationSettings.AppSettings("CheckSpec2Key")

    '    Dim strEmployID As String = ""
    '    If QueryCondition.Keys.Contains("employID") = True Then
    '        strEmployID = QueryCondition("employID").ToString()
    '    End If

    '    'Dim strSQL As String = "SELECT c.togetherflag,c.Containername,c.parentcontainerid,c.status, pb.Productname, p.Productrevision, sb.Specname,s.Specrevision," & vbCrLf & _
    '    '                       "c.Qty, NVL(wrh.Qty,0) AS ReportQty,NVL(cch.Nonsenseqty,0) AS Nonsenseqty,NVL(cch.Eligibilityqty,0) AS EligibleQty, p.Description AS ProductDesc," & vbCrLf & _
    '    '                       "c.Containerid, s.Specid, wfs.Islaststep, c.Childcount, wrh.reporttype2, s.iskey, c.holdreasonid, wrh.mutualcheckemployeeid, e.employeename, op.operationname," & vbCrLf & _
    '    '                       "f.factoryname,wrh.workingreporthistoryid,e1.fullname as reportname  FROM Container c " & vbCrLf & _
    '    '                       "INNER JOIN Product p ON c.Productid = p.Productid " & vbCrLf & _
    '    '                       "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid " & vbCrLf & _
    '    '                       "INNER JOIN Currentstatus cs ON cs.Currentstatusid = c.Currentstatusid " & vbCrLf & _
    '    '                       "INNER JOIN Spec s ON cs.Specid = s.Specid " & vbCrLf & _
    '    '                       "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid " & vbCrLf & _
    '    '                       "INNER JOIN operation op ON op.operationid = s.operationid " & vbCrLf & _
    '    '                       "LEFT JOIN Factory f ON f.Factoryid = cs.Factoryid " & vbCrLf & _
    '    '                       "LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid " & vbCrLf & _
    '    '                       "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid " & vbCrLf & _
    '    '                       "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid " & vbCrLf & _
    '    '                       "INNER JOIN historymainline h ON h.containerid=c.containerid AND h.workflowstepid=cs.workflowstepid " & vbCrLf & _
    '    '                       "INNER JOIN Workingreporthistory wrh ON h.historymainlineid= wrh.historymainlineid " & vbCrLf & _
    '    '                       "LEFT  JOIN Conventioncheckhistory cch ON wrh.Workingreporthistoryid = cch.Workingreportid " & vbCrLf & _
    '    '                       "LEFT  JOIN employee e ON e.employeeid=wrh.mutualcheckemployeeid " & vbCrLf & _
    '    '                       "LEFT  JOIN employee e1 ON e1.employeeid=h.employeeid " & vbCrLf & _
    '    '                       "WHERE  h.reversalstatus = 1  AND  NVL(cch.Nonsenseqty,0)=0 AND  NVL(cch.Eligibilityqty,0)=0 AND (wrh.mutualcheckemployeeid='" & strEmployID & "'  OR wrh.mutualcheckemployeeid IS NULL) " & vbCrLf & _
    '    '                       "AND f.Factoryname = '" & strFactoryName & "' And c.Status = 1 And c.containerid<>c.containername  And c.Parentcontainerid Is NULL "


    '    Dim strSQL As String = "SELECT c.togetherflag,c.Containername,c.parentcontainerid,c.status, pb.Productname, p.Productrevision, sb.Specname,s.Specrevision, " & vbCrLf & _
    '                               "c.Qty, NVL(wrh.Qty,0) AS ReportQty,NVL(cch.Nonsenseqty,0) AS Nonsenseqty,NVL(cch.Eligibilityqty,0) AS EligibleQty,  " & vbCrLf & _
    '                               "p.Description AS ProductDesc,c.Containerid, s.Specid, wfs.Islaststep, c.Childcount, wrh.reporttype2, s.iskey, c.holdreasonid,  " & vbCrLf & _
    '                               "wrh.mutualcheckemployeeid, e.employeename, op.operationname, NVL(Z.Qty,0) AS Disqualificationqty, " & vbCrLf & _
    '                               "f.factoryname,wrh.workingreporthistoryid,e1.fullname as reportname   " & vbCrLf & _
    '                           "FROM Container c INNER JOIN Product p ON c.Productid = p.Productid " & vbCrLf & _
    '                                           "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid " & vbCrLf & _
    '                                           "INNER JOIN Currentstatus cs ON cs.Currentstatusid = c.Currentstatusid " & vbCrLf & _
    '                                           "INNER JOIN Spec s ON cs.Specid = s.Specid " & vbCrLf & _
    '                                           "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid " & vbCrLf & _
    '                                           "INNER JOIN operation op ON op.operationid = s.operationid " & vbCrLf & _
    '                                           "LEFT JOIN Factory f ON f.Factoryid = cs.Factoryid " & vbCrLf & _
    '                                           "LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid " & vbCrLf & _
    '                                           "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid " & vbCrLf & _
    '                                           "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid " & vbCrLf & _
    '                                           "INNER JOIN historymainline h ON h.containerid=c.containerid AND h.workflowstepid=cs.workflowstepid " & vbCrLf & _
    '                                           "INNER JOIN Workingreporthistory wrh ON h.historymainlineid= wrh.historymainlineid " & vbCrLf & _
    '                                           "LEFT  JOIN Conventioncheckhistory cch ON wrh.Workingreporthistoryid = cch.Workingreportid " & vbCrLf & _
    '                                           "LEFT  JOIN employee e ON e.employeeid=wrh.mutualcheckemployeeid " & vbCrLf & _
    '                                           "LEFT  JOIN employee e1 ON e1.employeeid=h.employeeid " & vbCrLf & _
    '                                           "LEFT  JOIN (SELECT workingreportid,SUM(QTY) AS QTY FROM qualityrecord GROUP BY workingreportid) Z ON wrh.workingreporthistoryid = z.workingreportid " & vbCrLf & _
    '                           "WHERE(h.reversalstatus = 1 AND NVL(cch.Nonsenseqty, 0) = 0) " & vbCrLf & _
    '                                                       "AND  NVL(cch.Eligibilityqty,0)=0 " & vbCrLf & _
    '                                                       "AND (wrh.mutualcheckemployeeid='" & strEmployID & "'  OR wrh.mutualcheckemployeeid IS NULL) " & vbCrLf & _
    '                                                       "AND f.Factoryname = '" & strFactoryName & "' " & vbCrLf & _
    '                                                       "AND c.Status = 1 " & vbCrLf & _
    '                                                       "AND c.containerid<>c.containername  " & vbCrLf & _
    '                                                       "AND c.Parentcontainerid Is NULL " & vbCrLf & _
    '                                                       "AND NVL(z.Qty,0) < NVL(wrh.Qty,0) "


    '    If strTogetherFlag.Trim() <> "" Then
    '        strSQL += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
    '        strSQL += "AND pb.Productname = '" & strProductName & "'" & vbCrLf
    '    Else
    '        If strContainerName <> "" Then
    '            strSQL += "AND c.ContainerName = '" & strContainerName & "'" & vbCrLf
    '        Else
    '            If strSContainerName.Trim() <> "" Then
    '                strSQL += "AND c.ContainerName like '%" & strSContainerName & "%'" & vbCrLf
    '            End If
    '        End If
    '        If strProductName <> "" Then
    '            strSQL += "AND pb.Productname LIKE '%" & strProductName & "%'" & vbCrLf
    '        End If
    '        If strSpecName <> "" Then
    '            strSQL += "AND sb.Specname LIKE '%" & strSpecName & "%'" & vbCrLf
    '        End If

    '    End If

    '    strSQL = strSQL & " ORDER BY c.Containername, sb.Specname "

    '    Dim paraPage As New uMESPagingDataDTO
    '    paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
    '    paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
    '    paraPage.strSQL = strSQL
    '    Dim result As New uMESProcessResult

    '    oResult = dal.RunProcedureForPage(paraPage)
    '    Dim dt As New DataTable
    '    dt = oResult.DBTable.Copy

    '    dt.Columns.Add("NeedCheckQty")
    '    dt.Columns.Add("reporttype")
    '    dt.Columns.Add("iskeyName")

    '    For Each row As DataRow In dt.Rows
    '        Dim intNeedCheckQty As Integer = Convert.ToInt32(row("ReportQty")) - Convert.ToInt32(row("Nonsenseqty")) - Convert.ToInt32(row("EligibleQty"))
    '        row("NeedCheckQty") = intNeedCheckQty
    '        If row.IsNull("iskey") = False Then
    '            If row("iskey") = "1" Then
    '                row("iskeyName") = "G"
    '            Else
    '                row("iskeyName") = " "
    '            End If
    '        Else
    '            row("iskeyName") = " "
    '        End If

    '        If row("reporttype2") = "0" Then
    '            row("reporttype") = "首检"
    '        End If
    '        If row.IsNull("holdreasonid") = True Then
    '            row("holdreasonid") = "0"
    '        Else
    '            row("holdreasonid") = "1"
    '        End If
    '    Next

    '    If dt.Rows.Count > 0 Then
    '        oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dt)
    '    End If
    '    result.Result = True
    '    Return oResult
    'End Function

    Public Function GetContainerCurrentInfoNew(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetContainerCurrentInfoNew
        Dim oResult As New uMESProcessResult

        Dim strTogetherFlag As String = ""
        Dim strContainerName As String = ""
        Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

        Dim strProductName As String = ""
        If QueryCondition.Keys.Contains("ProductName") = True Then
            strProductName = QueryCondition("ProductName").ToString()
        End If

        If strProductName.Trim() = "" Then
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = commBusiness.ReplaceContainerName(QueryCondition("ContainerName").ToString.Trim())

                If strContainerName.Trim() <> "" Then
                    Dim r As DataTable = GetContainerInfo(strContainerName)
                    If r.Rows(0).IsNull("TogetherFlag") = False Then
                        strTogetherFlag = r.Rows(0)("TogetherFlag").ToString
                        strProductName = r.Rows(0)("productname").ToString
                    End If
                End If
            End If
        End If

        Dim strSContainerName As String = ""
        If QueryCondition.Keys.Contains("SearchContainerName") = True Then
            strSContainerName = QueryCondition("SearchContainerName").ToString()
        End If

        Dim strSpecName As String = ""
        If QueryCondition.Keys.Contains("SpecName") = True Then
            strSpecName = QueryCondition("SpecName").ToString()
        End If

        Dim strSpecNo As String = ""
        If QueryCondition.Keys.Contains("specno") = True Then
            strSpecNo = QueryCondition("specno").ToString()
        End If

        Dim strFactoryName As String = ""
        If QueryCondition.Keys.Contains("FactoryName") = True Then
            strFactoryName = QueryCondition("FactoryName").ToString()
        End If
        Dim strWorkCenterName As String = ""
        If QueryCondition.Keys.Contains("WorkCenterName") = True Then
            strWorkCenterName = QueryCondition("WorkCenterName").ToString()
        End If

        Dim strEmployID As String = ""
        If QueryCondition.Keys.Contains("employID") = True Then
            strEmployID = QueryCondition("employID").ToString()
        End If

        'Dim strSQL As String = "SELECT c.togetherflag,c.Containername,c.parentcontainerid,c.status, pb.Productname, p.Productrevision, sb.Specname,s.Specrevision," & vbCrLf & _
        '                       "c.Qty, NVL(wrh.Qty,0) AS ReportQty,NVL(cch.Nonsenseqty,0) AS Nonsenseqty,NVL(cch.Eligibilityqty,0) AS EligibleQty, p.Description AS ProductDesc," & vbCrLf & _
        '                       "c.Containerid, s.Specid, wfs.Islaststep, c.Childcount, wrh.reporttype2, s.iskey, c.holdreasonid, wrh.mutualcheckemployeeid, e.employeename, op.operationname," & vbCrLf & _
        '                       "f.factoryname,wrh.workingreporthistoryid,e1.fullname as reportname  FROM Container c " & vbCrLf & _
        '                       "INNER JOIN Product p ON c.Productid = p.Productid " & vbCrLf & _
        '                       "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid " & vbCrLf & _
        '                       "INNER JOIN Currentstatus cs ON cs.Currentstatusid = c.Currentstatusid " & vbCrLf & _
        '                       "INNER JOIN Spec s ON cs.Specid = s.Specid " & vbCrLf & _
        '                       "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid " & vbCrLf & _
        '                       "INNER JOIN operation op ON op.operationid = s.operationid " & vbCrLf & _
        '                       "LEFT JOIN Factory f ON f.Factoryid = cs.Factoryid " & vbCrLf & _
        '                       "LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid " & vbCrLf & _
        '                       "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid " & vbCrLf & _
        '                       "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid " & vbCrLf & _
        '                       "INNER JOIN historymainline h ON h.containerid=c.containerid AND h.workflowstepid=cs.workflowstepid " & vbCrLf & _
        '                       "INNER JOIN Workingreporthistory wrh ON h.historymainlineid= wrh.historymainlineid " & vbCrLf & _
        '                       "LEFT  JOIN Conventioncheckhistory cch ON wrh.Workingreporthistoryid = cch.Workingreportid " & vbCrLf & _
        '                       "LEFT  JOIN employee e ON e.employeeid=wrh.mutualcheckemployeeid " & vbCrLf & _
        '                       "LEFT  JOIN employee e1 ON e1.employeeid=h.employeeid " & vbCrLf & _
        '                       "WHERE  h.reversalstatus = 1  AND  NVL(cch.Nonsenseqty,0)=0 AND  NVL(cch.Eligibilityqty,0)=0 AND (wrh.mutualcheckemployeeid='" & strEmployID & "'  OR wrh.mutualcheckemployeeid IS NULL) " & vbCrLf & _
        '                       "AND f.Factoryname = '" & strFactoryName & "' And c.Status = 1 And c.containerid<>c.containername  And c.Parentcontainerid Is NULL "

        Dim strSQL As String = "SELECT c.togetherflag,c.Containername,c.parentcontainerid,c.status, pb.Productname, p.Productrevision, sb.Specname,s.Specrevision, " & vbCrLf & _
                                    "c.Qty, NVL(wrh.Qty,0) AS ReportQty,NVL(cch.Nonsenseqty,0) AS Nonsenseqty,NVL(cch.Eligibilityqty,0) AS EligibleQty,  " & vbCrLf & _
                                    "p.Description AS ProductDesc,c.Containerid, s.Specid, wfs.Islaststep, c.Childcount, wrh.reporttype2, s.iskey, c.holdreasonid,  " & vbCrLf & _
                                    "wrh.mutualcheckemployeeid, e.employeename, op.operationname, NVL(Z.Qty,0) AS Disqualificationqty, " & vbCrLf & _
                                    "f.factoryname,wrh.workingreporthistoryid,e1.fullname as reportname   " & vbCrLf & _
                                "FROM Container c INNER JOIN Product p ON c.Productid = p.Productid " & vbCrLf & _
                                                "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid " & vbCrLf & _
                                                "INNER JOIN Currentstatus cs ON cs.Currentstatusid = c.Currentstatusid " & vbCrLf & _
                                                "INNER JOIN Spec s ON cs.Specid = s.Specid " & vbCrLf & _
                                                "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid " & vbCrLf & _
                                                "INNER JOIN operation op ON op.operationid = s.operationid " & vbCrLf & _
                                                "LEFT JOIN Factory f ON f.Factoryid = cs.Factoryid " & vbCrLf & _
                                                "LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid " & vbCrLf & _
                                                "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid " & vbCrLf & _
                                                "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid " & vbCrLf & _
                                                "INNER JOIN historymainline h ON h.containerid=c.containerid AND h.workflowstepid=cs.workflowstepid " & vbCrLf & _
                                                "INNER JOIN Workingreporthistory wrh ON h.historymainlineid= wrh.historymainlineid " & vbCrLf & _
                                                "LEFT  JOIN Conventioncheckhistory cch ON wrh.Workingreporthistoryid = cch.Workingreportid " & vbCrLf & _
                                                "LEFT  JOIN employee e ON e.employeeid=wrh.mutualcheckemployeeid " & vbCrLf & _
                                                "LEFT  JOIN employee e1 ON e1.employeeid=wrh.reportemployeeid " & vbCrLf & _
                                                "LEFT  JOIN (SELECT workingreportid,SUM(QTY) AS QTY FROM qualityrecord GROUP BY workingreportid) Z ON wrh.workingreporthistoryid = z.workingreportid " & vbCrLf & _
                                "WHERE(h.reversalstatus = 1 AND NVL(cch.Nonsenseqty, 0) = 0) " & vbCrLf & _
                                                            "AND  NVL(cch.Eligibilityqty,0)=0 " & vbCrLf & _
                                                            "AND (wrh.mutualcheckemployeeid='" & strEmployID & "'  OR wrh.mutualcheckemployeeid IS NULL) " & vbCrLf & _
                                                            "AND  f.Factoryname = '" & strFactoryName & "'" & vbCrLf & _
                                                            "AND c.Status = 1 " & vbCrLf & _
                                                            "AND c.containerid<>c.containername  " & vbCrLf & _
                                                            "AND c.Parentcontainerid Is NULL " & vbCrLf & _
                                                            "AND NVL(z.Qty,0) < NVL(wrh.Qty,0) "

        If strTogetherFlag.Trim() <> "" Then
            strSQL += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
            strSQL += "AND pb.Productname = '" & strProductName & "'" & vbCrLf
        Else
            If strContainerName <> "" Then
                strSQL += "AND c.ContainerName = '" & strContainerName & "'" & vbCrLf
            Else
                If strSContainerName.Trim() <> "" Then
                    strSQL += "AND c.ContainerName like '%" & strSContainerName & "%'" & vbCrLf
                End If
            End If
            If strProductName <> "" Then
                strSQL += "AND pb.Productname LIKE '%" & strProductName & "%'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL += "AND s.specno LIKE '%" & strSpecName & "%'" & vbCrLf
            End If

            If strSpecNo <> "" Then
                strSQL += "AND s.Specno = '" & strSpecNo & "'" & vbCrLf
            End If

        End If
        strSQL = strSQL & " ORDER BY c.Containername, sb.Specname "

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = strSQL
        Dim result As New uMESProcessResult

        oResult = dal.RunProcedureForPage(paraPage)
        Dim dt As New DataTable
        dt = oResult.DBTable.Copy

        dt.Columns.Add("NeedCheckQty")
        dt.Columns.Add("reporttype")
        dt.Columns.Add("iskeyName")

        For Each row As DataRow In dt.Rows
            Dim intNeedCheckQty As Integer = Convert.ToInt32(row("ReportQty")) - Convert.ToInt32(row("Nonsenseqty")) - Convert.ToInt32(row("EligibleQty"))
            row("NeedCheckQty") = intNeedCheckQty
            If row.IsNull("iskey") = False Then
                If row("iskey") = "1" Then
                    row("iskeyName") = "G"
                Else
                    row("iskeyName") = " "
                End If
            Else
                row("iskeyName") = " "
            End If

            If row("reporttype2") = "0" Then
                row("reporttype") = "首检"
            End If
            If row.IsNull("holdreasonid") = True Then
                row("holdreasonid") = "0"
            Else
                row("holdreasonid") = "1"
            End If
        Next
        If dt.Rows.Count > 0 Then
            oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dt)
        End If
        result.Result = True
        Return oResult
    End Function

#End Region


#Region "获取成品检验信息"
    Public Function GetFinishCheckContainerInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetFinishCheckContainerInfo
        Dim oResult As New uMESProcessResult

        Dim strTogetherFlag As String = ""
        Dim strContainerName As String = ""
        Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

        If QueryCondition.Keys.Contains("ContainerName") = True Then
            strContainerName = commBusiness.ReplaceContainerName(QueryCondition("ContainerName").ToString.Trim())

            If strContainerName.Trim() <> "" Then
                Dim r As DataTable = GetContainerInfo(strContainerName)
                If r.Rows(0).IsNull("TogetherFlag") = False Then
                    strTogetherFlag = r.Rows(0)("TogetherFlag").ToString
                End If
            End If
        End If

        Dim strSContainerName As String = ""
        If QueryCondition.Keys.Contains("SearchContainerName") = True Then
            strSContainerName = QueryCondition("SearchContainerName").ToString()
        End If
        Dim strProductName As String = ""
        If QueryCondition.Keys.Contains("ProductName") = True Then
            strProductName = QueryCondition("ProductName").ToString()
        End If
        Dim strSpecName As String = ""
        If QueryCondition.Keys.Contains("SpecName") = True Then
            strSpecName = QueryCondition("SpecName").ToString()
        End If
        Dim strFactoryName As String = ""
        If QueryCondition.Keys.Contains("FactoryName") = True Then
            strFactoryName = QueryCondition("FactoryName").ToString()
        End If
        Dim strWorkCenterName As String = ""
        If QueryCondition.Keys.Contains("WorkCenterName") = True Then
            strWorkCenterName = QueryCondition("WorkCenterName").ToString()
        End If


        Dim strCheck2SpecKey As String = ConfigurationSettings.AppSettings("CheckSpec2Key")



        Dim strSQL As String = "SELECT c.togetherflag,c.Containername,c.parentcontainerid,c.status, pb.Productname, p.Productrevision, sb.Specname,s.Specrevision, c.Qty, 0 AS ReportQty," & vbCrLf & _
                               "0 AS Nonsenseqty, 0 AS EligibleQty,p.Description AS ProductDesc, c.Containerid, s.Specid, wfs.Islaststep,c.Childcount, 4 AS reporttype2,s.iskey," & vbCrLf & _
                               "c.holdreasonid ,'' AS mutualcheckemployeeid,'' AS employeename,op.operationname,f.factoryname " & vbCrLf & _
                               "FROM Container c " & vbCrLf & _
                               "INNER JOIN Product p ON c.Productid = p.Productid " & vbCrLf & _
                               "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid " & vbCrLf & _
                               "INNER JOIN Currentstatus cs ON cs.Currentstatusid = c.Currentstatusid " & vbCrLf & _
                               "INNER JOIN Spec s ON cs.Specid = s.Specid " & vbCrLf & _
                               "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid " & vbCrLf & _
                               "INNER JOIN operation op ON op.operationid = s.operationid " & vbCrLf & _
                               "LEFT JOIN Factory f ON f.Factoryid = cs.Factoryid " & vbCrLf & _
                               "LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid " & vbCrLf & _
                               "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid " & vbCrLf & _
                               "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid " & vbCrLf & _
                               "WHERE  op.operationname='" & strCheck2SpecKey & "' " & vbCrLf & _
                               "AND  f.Factoryname = '" & strFactoryName & "' And c.Status = 1 And c.containerid<>c.containername  And c.Parentcontainerid Is NULL "


        If strTogetherFlag.Trim() <> "" Then
            strSQL += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
        Else
            If strContainerName <> "" Then
                strSQL += "AND c.ContainerName = '" & strContainerName & "'" & vbCrLf
            Else
                If strSContainerName.Trim() <> "" Then
                    strSQL += "AND c.ContainerName like '%" & strSContainerName & "%'" & vbCrLf
                End If
            End If
            If strProductName <> "" Then
                strSQL += "AND pb.Productname LIKE '%" & strProductName & "%'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL += "AND sb.Specname LIKE '%" & strSpecName & "%'" & vbCrLf
            End If
        End If

        strSQL = strSQL & " ORDER BY c.Containername, sb.Specname "

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = strSQL
        Dim result As New uMESProcessResult

        oResult = dal.RunProcedureForPage(paraPage)
        Dim dt As New DataTable
        dt = oResult.DBTable.Copy

        dt.Columns.Add("NeedCheckQty")
        dt.Columns.Add("reporttype")
        dt.Columns.Add("iskeyName")

        For Each row As DataRow In dt.Rows
            Dim intNeedCheckQty As Integer = Convert.ToInt32(row("ReportQty")) - Convert.ToInt32(row("Nonsenseqty")) - Convert.ToInt32(row("EligibleQty"))
            row("NeedCheckQty") = intNeedCheckQty
            If row("iskey") = "1" Then
                row("iskeyName") = "G"
            Else
                row("iskeyName") = " "
            End If
            If row("reporttype2") = "0" Then
                row("reporttype") = "首检"
            End If
            If row.IsNull("holdreasonid") = True Then
                row("holdreasonid") = "0"
            Else
                row("holdreasonid") = "1"
            End If
        Next

        If dt.Rows.Count > 0 Then
            oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dt)
        End If
        result.Result = True
        Return oResult
    End Function
#End Region



    ''' <summary>
    ''' 根据批次和工序获取质量记载单信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetQualityRecordInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetQualityRecordInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strSQL = "SELECT Qualityrecord.Qualityrecordname, Container.Containername, Specbase.Specname," & vbCrLf & _
                    "       Spec.Specrevision, Employee.Fullname, TO_CHAR(Qualityrecord.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate, Container.Qty,Qualityrecord.RevokeNotes," & vbCrLf & _
                    "       DECODE(Qualityrecord.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS Recordstatus" & vbCrLf & _
                    "FROM Qualityrecord" & vbCrLf & _
                    "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
                    "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid " & vbCrLf & _
                    "WHERE Qualityrecord.Recordstatus <> 4 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "'" & vbCrLf
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "'" & vbCrLf
            End If

            strSQL = strSQL & "ORDER BY Qualityrecord.Checkdate DESC" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 根据批次和工序获取质量记载单信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetProductNoInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetProductNoInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strSQL = "SELECT Productnoinfo.Productno, QualityDisposeAdvice.Qualitydisposeadvicename, Employee.Fullname, TO_CHAR(Qualityrecord.Technologistdate, 'RRRR-MM-DD HH24:MI:SS') AS Technologistdate " & _
                    "FROM Qualityrecord " & _
                    "INNER JOIN Qualityrecordproductnoinfo ON Qualityrecord.Qualityrecordid = Qualityrecordproductnoinfo.Qualityrecordid " & _
                    "INNER JOIN Productnoinfo ON Qualityrecordproductnoinfo.Productnoinfoid = Productnoinfo.Productnoinfoid " & _
                    "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid " & _
                    "INNER JOIN Spec ON Spec.Specid = Qualityrecord.Specid " & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & _
                    "LEFT OUTER JOIN QualityDisposeAdvice ON QualityDisposeAdvice.Qualitydisposeadviceid = Productnoinfo.Qualitydisposeadviceid " & _
                    "LEFT OUTER JOIN Employee ON Qualityrecord.Technologistemployeeid = Employee.Employeeid " & _
                    "WHERE  Qualityrecord.Recordstatus <> 4 AND Productnoinfo.Productno <> '00000000' "
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "' "
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "' "
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "' "
            End If
            strSQL = strSQL & "ORDER BY Productnoinfo.Productno "

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 根据批次和工序获取报工信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetWorkRecord(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetWorkRecord
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strSQL = "SELECT Container.Containername, Productbase.Productname, CSpec.Specname, CSpec.Specrevision, Employee.Fullname, " & _
                    "       Historymainline.Txndate, WorkingReportHistory.Qty AS ReportQty, CEligible.Eligibilityqty AS EligibleQty, " & _
                    "       (NVL(WorkingReportHistory.Qty, 0) - NVL(CEligible.Eligibilityqty, 0)) AS NeedCheckQty " & _
                    "FROM WorkingReportHistory " & _
                    "INNER JOIN Container ON Historymainline.Containerid = Container.Containerid " & _
                    "INNER JOIN Product ON Container.Productid = Product.Productid " & _
                    "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid " & _
                    "INNER JOIN WorkCenterDispatching ON Workingreporthistory.Workcenterdispatchingid = Workingreporthistory.Workcenterdispatchingid  " & _
                    "INNER JOIN Spec ON WorkCenterDispatching.Specid = Spec.Specid " & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & _
                    "INNER JOIN Historymainline ON Workingreporthistory.Historymainlineid = Historymainline.Historymainlineid " & _
                    "LEFT OUTER JOIN  " & _
                    "(SELECT Conventioncheckhistory.Workingreportid, SUM(Conventioncheckhistory.Eligibilityqty) AS Eligibilityqty " & _
                    "FROM Historymainline   " & _
                    "INNER JOIN Conventioncheckhistory ON Historymainline.Historymainlineid = Conventioncheckhistory.Historymainlineid  " & _
                    "WHERE Historymainline.reversalstatus = 1 " & _
                    "GROUP BY Conventioncheckhistory.Workingreportid  " & _
                    ") CEligible  " & _
                    "ON CEligible.Workingreportid = WorkingReportHistory.Workingreporthistoryid " & _
                    "LEFT OUTER JOIN  " & _
                    "(SELECT Qualityrecord.Workingreportid " & _
                    "FROM Qualityrecord " & _
                    ") CQualityrecord " & _
                    "ON CQualityrecord.Workingreportid = WorkingReportHistory.Workingreporthistoryid " & _
                    "WHERE  Container.Status = 1 AND Historymainline.reversalstatus = 1 "
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "' "
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND CSpec.Specname = '" & strSpecName & "' "
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND CSpec.Specrevision = '" & strSpecRev & "' "
            End If
            strSQL = strSQL & "ORDER BY Container.Containername, CSpec.Specname "

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 根据批次和工序获取待检信息包括子批次
    ''' </summary>
    ''' <returns></returns>
    Private Function GetNeedCheckInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetNeedCheckInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev, strCheckType, strNonSenseQty As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strCheckType = ""
            If QueryCondition.Keys.Contains("CheckType") = True Then
                strCheckType = QueryCondition("CheckType").ToString()
            End If
            strNonSenseQty = ""
            If QueryCondition.Keys.Contains("NonSenseQty") = True Then
                strNonSenseQty = QueryCondition("NonSenseQty").ToString()
            End If
            strSQL = "SELECT c.Containername, c.Qty, pb.Productname, p.Productrevision, p.Description, sb.Specname," & vbCrLf & _
                    "   s.Specrevision, wrh.Qty AS ReportQty,cu.workflowstepid," & vbCrLf & _
                    "   (wrh.Qty - NVL(CEligible.Eligibilityqty,0) - NVL(CEligible.Nonsenseqty,0)) AS NeedCheckQty," & vbCrLf & _
                    "   NVL(CEligible.Eligibilityqty,0) AS EligibleQty, NVL(CEligible.Nonsenseqty,0) AS Nonsenseqty,e.employeeid, e.Fullname, r.Resourcename, r.ResourceEName," & vbCrLf & _
                    "   t.TeamName, TO_CHAR(hml.TxnDate, 'RRRR-MM-DD HH24:MI:SS') AS TxnDate, wrh.Workingreporthistoryid, wrh.Notes" & vbCrLf & _
                    "   ,wrh.historyid,DECODE(wrh.reporttype2,0,'首件',1,'中件',2,'末件',3,'其他') AS reporttype" & vbCrLf & _
                    "FROM Container c" & vbCrLf & _
                    "INNER JOIN Product p ON c.Productid = p.Productid" & vbCrLf & _
                    "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid" & vbCrLf & _
                    "INNER JOIN Currentstatus cu ON cu.Currentstatusid = c.Currentstatusid" & vbCrLf & _
                    "INNER JOIN Spec s ON cu.Specid = s.Specid" & vbCrLf & _
                    "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                    "INNER JOIN WorkCenterDispatching wcd ON wcd.Containerid = c.Containerid AND wcd.Specid = s.Specid" & vbCrLf & _
                    "INNER JOIN Workingreporthistory wrh ON wcd.Workcenterdispatchingid = wrh.Workcenterdispatchingid" & vbCrLf & _
                    "INNER JOIN Historymainline hml ON wrh.Historymainlineid = hml.Historymainlineid" & vbCrLf & _
                    "LEFT OUTER JOIN (" & vbCrLf & _
                    "   SELECT cch.Workingreportid, SUM(cch.Eligibilityqty) AS Eligibilityqty, SUM(cch.Nonsenseqty) AS Nonsenseqty" & vbCrLf & _
                    "   FROM Conventioncheckhistory cch" & vbCrLf & _
                    "   LEFT JOIN historymainline hml2 ON hml2.historymainlineid = cch.historymainlineid" & vbCrLf & _
                    "   WHERE hml2.reversalstatus = 1" & vbCrLf & _
                    "   GROUP BY cch.Workingreportid" & vbCrLf & _
                    ") CEligible  ON wrh.Workingreporthistoryid = CEligible.Workingreportid" & vbCrLf & _
                    "LEFT OUTER JOIN (" & vbCrLf & _
                    "   SELECT qr.Workingreportid, SUM(qr.Qty) AS Qty" & vbCrLf & _
                    "   FROM Qualityrecord qr" & vbCrLf & _
                    "   GROUP BY qr.Workingreportid" & vbCrLf & _
                    ") CQualityrecord  ON CQualityrecord.Workingreportid = wrh.Workingreporthistoryid" & vbCrLf & _
                    "INNER JOIN Employee e ON wrh.reportemployeeid = e.Employeeid" & vbCrLf & _
                    "LEFT OUTER JOIN Resourcedef r ON wrh.Resourceid = r.Resourceid" & vbCrLf & _
                    "LEFT OUTER JOIN Team t ON wrh.Teamid = t.Teamid" & vbCrLf & _
                    "WHERE c.Status =1 AND c.Parentcontainerid IS NULL AND wrh.Qty > 0 AND hml.reversalstatus = 1" & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND c.ContainerName in (" & strContainerName & ")" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND sb.Specname = '" & strSpecName & "'" & vbCrLf
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND s.Specrevision = '" & strSpecRev & "'" & vbCrLf
            End If
            If strNonSenseQty <> "" Then
                strSQL = strSQL & "AND CEligible.Nonsenseqty IS NOT NULL" & vbCrLf
            End If
            Select Case strCheckType
                Case 1
                    strSQL = strSQL & "AND wrh.Reporttype2 = '0'" & vbCrLf
                Case 2
                    strSQL = strSQL & "AND wrh.Reporttype2 IN (1,2,3)" & vbCrLf
            End Select
            strSQL = strSQL & "ORDER BY c.Containername, sb.Specname, hml.TxnDate "

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)

            Dim dtTmp As DataTable = dtMain.Clone()
            For Each row As DataRow In dtMain.Rows
                If Convert.ToInt32(row("NeedCheckQty")) > 0 Then
                    dtTmp.ImportRow(row)
                End If
            Next

            Dim ds As New DataSet
            ds.Tables.Add(dtTmp)
            ds.Tables(0).TableName = "dtMain"

            Dim strWorkingreporthistoryid As String = ""

            For i = 0 To dtTmp.Rows.Count - 1
                If dtMain.Rows(i).IsNull("Workingreporthistoryid") Then
                    Continue For
                End If
                strWorkingreporthistoryid = strWorkingreporthistoryid & dtMain.Rows(i).Item("Workingreporthistoryid") & "','"
            Next

            If strWorkingreporthistoryid.Length > 0 Then
                strWorkingreporthistoryid = strWorkingreporthistoryid.Substring(0, strWorkingreporthistoryid.Length - 3)
                strSQL = "SELECT Workingreporthistory.Workingreporthistoryid, WorkingReportHistoryChildConta.Childcontainer, " & _
                        "   NVL(CNoDispose.Status, 0) AS Status " & _
                        "FROM Workingreporthistory " & _
                        "INNER JOIN Historymainline ON Workingreporthistory.Historymainlineid = Historymainline.Historymainlineid " & _
                        "INNER JOIN WorkingReportHistoryChildConta ON WorkingReportHistoryChildConta.Workingreporthistoryid = Workingreporthistory.Workingreporthistoryid " & _
                        "LEFT OUTER JOIN  " & _
                        "(SELECT Conventioncheckhistory.Workingreportid, CheckPartNOList.PartNO, CheckPartNOList.Checkresulttype AS Status " & _
                        "FROM ConventionCheckhistory  " & _
                        "INNER JOIN ConventionCheckHistoryCheckPar ON ConventionCheckhistory.ConventionCheckhistoryid = ConventionCheckHistoryCheckPar.ConventionCheckhistoryid " & _
                        "INNER JOIN CheckPartNOList ON ConventionCheckHistoryCheckPar.Checkpartnolistid = CheckPartNOList.Checkpartnolistid " & _
                        ") CNoDispose " & _
                        "ON Workingreporthistory.Workingreporthistoryid = CNoDispose.Workingreportid " & _
                        "AND WorkingReportHistoryChildConta.Childcontainer = CNoDispose.PartNO " & _
                        "WHERE Historymainline.reversalstatus =1 AND Workingreporthistory.Workingreporthistoryid IN ('" & strWorkingreporthistoryid & "') " & _
                        "ORDER BY Workingreporthistory.Workingreporthistoryid, WorkingReportHistoryChildConta.Sequence "
                Dim dtContainerChild As DataTable = dal.GetQualityRecordInfo(strSQL)
                If dtContainerChild.Rows.Count > 0 Then
                    ds.Tables.Add(dtContainerChild)
                    ds.Tables(1).TableName = "dtContainerChild"
                End If
            End If






            'If dtMain.Rows.Count > 0 Then
            '    Dim strWorkingreporthistoryid As String = ""
            '    Dim strhisid As String = ""
            '    For i = 0 To dtMain.Rows.Count - 1
            '        If dtMain.Rows(i).IsNull("Workingreporthistoryid") Then
            '            Continue For
            '        End If
            '        If strhisid <> "" Then
            '            If strhisid = dtMain.Rows(i)("historyid") Then
            '                dtMain.Rows(i)("historyid") = 0
            '                Continue For
            '            Else
            '                strhisid = dtMain.Rows(i)("historyid")
            '            End If
            '        Else
            '            strhisid = dtMain.Rows(i)("historyid")
            '        End If
            '        strWorkingreporthistoryid = strWorkingreporthistoryid & dtMain.Rows(i).Item("Workingreporthistoryid") & "','"
            '    Next
            '    Dim dv = dtMain.DefaultView
            '    dv.RowFilter = "historyid<>'0'"
            '    ds.Tables.Add(dv.ToTable)
            '    ds.Tables(0).TableName = "dtMain"
            '    If strWorkingreporthistoryid.Length > 0 Then
            '        strWorkingreporthistoryid = strWorkingreporthistoryid.Substring(0, strWorkingreporthistoryid.Length - 3)
            '        strSQL = "SELECT Workingreporthistory.Workingreporthistoryid, WorkingReportHistoryChildConta.Childcontainer, " & _
            '                "   NVL(CNoDispose.Status, 0) AS Status " & _
            '                "FROM Workingreporthistory " & _
            '                "INNER JOIN Historymainline ON Workingreporthistory.Historymainlineid = Historymainline.Historymainlineid " & _
            '                "INNER JOIN WorkingReportHistoryChildConta ON WorkingReportHistoryChildConta.Workingreporthistoryid = Workingreporthistory.Workingreporthistoryid " & _
            '                "LEFT OUTER JOIN  " & _
            '                "(SELECT Conventioncheckhistory.Workingreportid, CheckPartNOList.PartNO, CheckPartNOList.Checkresulttype AS Status " & _
            '                "FROM ConventionCheckhistory  " & _
            '                "INNER JOIN ConventionCheckHistoryCheckPar ON ConventionCheckhistory.ConventionCheckhistoryid = ConventionCheckHistoryCheckPar.ConventionCheckhistoryid " & _
            '                "INNER JOIN CheckPartNOList ON ConventionCheckHistoryCheckPar.Checkpartnolistid = CheckPartNOList.Checkpartnolistid " & _
            '                ") CNoDispose " & _
            '                "ON Workingreporthistory.Workingreporthistoryid = CNoDispose.Workingreportid " & _
            '                "AND WorkingReportHistoryChildConta.Childcontainer = CNoDispose.PartNO " & _
            '                "WHERE Historymainline.reversalstatus =1 AND Workingreporthistory.Workingreporthistoryid IN ('" & strWorkingreporthistoryid & "') " & _
            '                "ORDER BY Workingreporthistory.Workingreporthistoryid, WorkingReportHistoryChildConta.Sequence "
            '        Dim dtContainerChild As DataTable = dal.GetQualityRecordInfo(strSQL)
            '        If dtContainerChild.Rows.Count > 0 Then
            '            ds.Tables.Add(dtContainerChild)
            '            ds.Tables(1).TableName = "dtContainerChild"
            '        End If
            '    End If
            'End If

            oResult.DBDataSet = ds
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 根据批次和工序获取待检信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetNeedWorkingReportInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetNeedWorkingReportInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If

            strSQL = "SELECT Container.Containername, Container.Qty, Productbase.Productname, Product.Productrevision, " & _
                    "Product.Description, Specbase.Specname,Spec.Specrevision, Workingreporthistory.Qty AS ReportQty,  " & _
                    " (Workingreporthistory.Qty - NVL(CEligible.Eligibilityqty,0)-NVL(CEligible.Nonsenseqty,0)) AS NeedCheckQty,  " & _
                    " NVL(CEligible.Eligibilityqty,0) AS EligibleQty, NVL(CEligible.Nonsenseqty,0) AS Nonsenseqty, Employee.Fullname, Resourcedef.Resourcename, Resourcedef.ResourceEName, " & _
                    " Team.TeamName, TO_CHAR(Historymainline.TxnDate, 'RRRR-MM-DD HH24:MI:SS') AS TxnDate, Workingreporthistory.Workingreporthistoryid, Workingreporthistory.Notes " & _
                    "FROM Container " & _
                    "INNER JOIN Product ON Container.Productid = Product.Productid " & _
                    "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid " & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid " & _
                    "INNER JOIN Spec ON Currentstatus.Specid = Spec.Specid " & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & _
                    "INNER JOIN WorkCenterDispatching ON WorkCenterDispatching.Containerid = Container.Containerid  " & _
                    "AND WorkCenterDispatching.Specid = Spec.Specid  " & _
                    "INNER JOIN Workingreporthistory ON WorkCenterDispatching.Workcenterdispatchingid = Workingreporthistory.Workcenterdispatchingid  " & _
                    "INNER JOIN Historymainline ON Workingreporthistory.Historymainlineid = Historymainline.Historymainlineid " & _
                    "LEFT OUTER JOIN " & _
                    "(" & vbCrLf & _
                    "SELECT Conventioncheckhistory.Workingreportid, SUM(Conventioncheckhistory.Eligibilityqty) AS Eligibilityqty, SUM(Conventioncheckhistory.Nonsenseqty) AS Nonsenseqty" & vbCrLf & _
                    "FROM Conventioncheckhistory" & vbCrLf & _
                    "GROUP BY Conventioncheckhistory.Workingreportid " & vbCrLf & _
                    ") CEligible " & _
                    " ON CEligible.Workingreportid = Workingreporthistory.Workingreporthistoryid  " & _
                    "INNER JOIN Employee ON Historymainline.Employeeid = Employee.Employeeid " & _
                    "LEFT OUTER JOIN Resourcedef ON Workingreporthistory.Resourceid = Resourcedef.Resourceid " & _
                    "LEFT OUTER JOIN Team ON Workingreporthistory.Teamid = Team.Teamid " & _
                    "WHERE Container.Status =1 AND Container.Parentcontainerid IS NULL AND Workingreporthistory.Qty > 0 AND Historymainline.reversalstatus = 1 "
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "' "
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "' "
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "' "
            End If

            strSQL = strSQL & "ORDER BY Container.Containername, Specbase.Specname, Historymainline.TxnDate "

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)
            dtMain.TableName = "dtMain"

            oResult.DBTable = dtMain
            oResult.Result = True
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message

        End Try
        Return oResult
    End Function

    ''' <summary>
    ''' 执行常规检验
    ''' </summary>
    ''' <param name="oRoutineCheck">执行常规检验所需参数</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RunRoutineCheck(ByVal oRoutineCheck As uMESRoutineCheckDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESCheckMainIBusiness.RunRoutineCheck
        RunRoutineCheck = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oRoutineCheck.ServerHostInfo

            Dim strTxnDocName, strTxnName, strSubentityListName As String
            strTxnDocName = "ConventionCheckDoc"
            strTxnName = "ConventionCheck"
            strSubentityListName = "CheckPartNOList"

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = oRoutineCheck.ContainerName
            dataEntity.ClientOtherValue = ""
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "WorkingReportID"
            dataEntity.ClientDataValue = oRoutineCheck.WorkingReportID
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "EligibilityQty"
            dataEntity.ClientDataValue = oRoutineCheck.EligibilityQty
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "NonsenseQty"
            dataEntity.ClientDataValue = oRoutineCheck.NonsenseQty
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "CheckType"
            dataEntity.ClientDataValue = oRoutineCheck.CheckType
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Notes"
            dataEntity.ClientDataValue = oRoutineCheck.Notes
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ReportEmployee"
            dataEntity.ClientDataValue = oRoutineCheck.ReportEmployee
            m_DataList.Add(dataEntity)
            Dim m_ChildList As New Dictionary(Of String, List(Of ClientAPIEntity))

            Dim dtChildList As DataTable = oRoutineCheck.ProductNoInfo

            If dtChildList IsNot Nothing Then
                If dtChildList.Rows.Count > 0 Then
                    For i As Integer = 0 To dtChildList.Rows.Count - 1
                        Dim m_Child As New List(Of ClientAPIEntity)
                        Dim stepEntity As New ClientAPIEntity

                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
                        stepEntity.ClientDataName = "PartNO"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("PartNO").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
                        stepEntity.ClientDataName = "CheckResultType"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("CheckResultType").ToString
                        m_Child.Add(stepEntity)
                        m_ChildList.Add(i.ToString, m_Child)
                    Next
                End If
            End If

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)

            If dtChildList IsNot Nothing Then
                If dtChildList.Rows.Count > 0 Then
                    success = clientData.RunTxnServiceHasSubentity(strTxnDocName, strTxnName, m_DataList, strSubentityListName, m_ChildList, strInfo)
                Else
                    success = clientData.RunTxnService(strTxnDocName, strTxnName, m_DataList, strInfo)
                End If
            Else
                success = clientData.RunTxnService(strTxnDocName, strTxnName, m_DataList, strInfo)
            End If

            If success = True Then
                RunRoutineCheck = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 获取工序总检信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetSpecLastCheckInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetSpecLastCheckInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If
            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strSQL = "SELECT Container.Containername, Container.Qty, Productbase.Productname, Product.Productrevision, Product.Description, Specbase.Specname,  " & _
            "       Spec.Specrevision, Container.Qty, Workingreporthistory.Qty AS ReportQty,   " & _
            "       (Workingreporthistory.Qty - NVL(SUM(Conventioncheckhistory.Eligibilityqty),0) - NVL(SUM(Conventioncheckhistory.Nonsenseqty),0)) AS NeedCheckQty,   " & _
            "       NVL(SUM(Conventioncheckhistory.Eligibilityqty),0) AS EligibleQty, NVL(SUM(Conventioncheckhistory.Nonsenseqty),0) AS Nonsenseqty, Employee.Fullname, Resourcedef.Resourcename, Resourcedef.Resourcenum,  " & _
            "       Team.TeamName, TO_CHAR(Historymainline.TxnDate, 'RRRR-MM-DD HH24:MI:SS') AS TxnDate, Workingreporthistory.Workingreporthistoryid, Workingreporthistory.Notes  " & _
            "FROM Container  " & _
            "INNER JOIN Product ON Container.Productid = Product.Productid  " & _
            "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid  " & _
            "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid  " & _
            "INNER JOIN Spec ON Currentstatus.Specid = Spec.Specid  " & _
            "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid  " & _
            "INNER JOIN WorkCenterDispatching ON WorkCenterDispatching.Containerid = Container.Containerid  " & _
            "AND WorkCenterDispatching.Specid = Spec.Specid  " & _
            "INNER JOIN Workingreporthistory ON WorkCenterDispatching.Workcenterdispatchingid = Workingreporthistory.Workcenterdispatchingid  " & _
            "INNER JOIN Historymainline ON Workingreporthistory.Historymainlineid = Historymainline.Historymainlineid " & _
            "LEFT OUTER JOIN Conventioncheckhistory ON Workingreporthistory.Workingreporthistoryid = Conventioncheckhistory.Workingreportid  " & _
            "INNER JOIN Employee ON Historymainline.Employeeid = Employee.Employeeid  " & _
            "LEFT OUTER JOIN Resourcedef ON Workingreporthistory.Resourceid = Resourcedef.Resourceid  " & _
            "LEFT OUTER JOIN Team ON Workingreporthistory.Teamid = Team.Teamid  " & _
            "WHERE Container.Status =1 AND Container.Parentcontainerid IS NULL AND Workingreporthistory.Qty > 0 AND  Historymainline.reversalstatus = 1 "
            If strContainerName <> "" Then
                strSQL = strSQL & "   AND Container.ContainerName = '" & strContainerName & "' "
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "   AND Specbase.Specname = '" & strSpecName & "' "
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "   AND Spec.Specrevision = '" & strSpecRev & "' "
            End If
            strSQL = strSQL & "GROUP BY Container.Containername, Productbase.Productname, Product.Productrevision, Specbase.Specname,  " & _
            "       Spec.Specrevision, Container.Qty, Product.Description, Workingreporthistory.Qty, Employee.Fullname, Resourcedef.Resourcename, Resourcedef.Resourcenum,  " & _
            "       Team.TeamName, Historymainline.TxnDate, Workingreporthistory.Workingreporthistoryid, Workingreporthistory.Notes  " & _
            "ORDER BY Container.Containername, Specbase.Specname, Historymainline.TxnDate "

            Dim dtWorkRecord As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtWorkRecord = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtWorkRecord)

            strSQL = "SELECT Qualityrecord.Qualityrecordname, Container.Containername, Specbase.Specname," & vbCrLf & _
                 "       Spec.Specrevision, Employee.Fullname, TO_CHAR(Qualityrecord.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate, Qualityrecord.Qty, " & vbCrLf & _
                 "       DECODE(Qualityrecord.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS Recordstatus, " & vbCrLf & _
                 "       TechnologistEmployee.Fullname AS TechnologistName, Qualityrecord.Technologistdate, Qualityrecord.Qualityrecordid,qualityrecord.revokenotes, " & vbCrLf & _
                 "       (CASE WHEN temp.NonScrapQty IS NULL THEN 0 ELSE temp.NonScrapQty END) AS NonScrapQty,(CASE WHEN temp2.ToScrapQty IS NULL THEN 0 ELSE temp2.ToScrapQty END) AS ToScrapQty " & vbCrLf & _
                 "FROM Qualityrecord" & vbCrLf & _
                 "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
                 "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
                 "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                 "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid " & vbCrLf & _
                 "LEFT OUTER JOIN Employee TechnologistEmployee ON TechnologistEmployee.Employeeid = Qualityrecord.Technologistemployeeid " & vbCrLf & _
                 "LEFT OUTER JOIN ( " & vbCrLf & _
                 "SELECT Qualityrecord.Qualityrecordid,SUM((CASE WHEN Productnoinfo.Qty IS NULL THEN 1 ELSE Productnoinfo.Qty END)) AS NonScrapQty " & vbCrLf & _
                 "FROM Qualityrecord " & vbCrLf & _
                 "LEFT JOIN QualityRecordProductNoInfo ON Qualityrecord.Qualityrecordid = QualityRecordProductNoInfo.Qualityrecordid " & vbCrLf & _
                 "LEFT JOIN Productnoinfo ON Productnoinfo.Productnoinfoid = QualityRecordProductNoInfo.Productnoinfoid " & vbCrLf & _
                 "LEFT JOIN Qualitydisposeadvice ON Qualitydisposeadvice.Qualitydisposeadviceid = Productnoinfo.Qualitydisposeadviceid  " & vbCrLf & _
                 "WHERE Qualitydisposeadvicename <> '报废' " & vbCrLf & _
                 "GROUP BY Qualityrecord.Qualityrecordid " & vbCrLf & _
                 ") temp ON temp.Qualityrecordid = Qualityrecord.Qualityrecordid " & vbCrLf & _
                 "LEFT OUTER JOIN ( " & vbCrLf & _
                 "SELECT Qualityrecord.Qualityrecordid,SUM((CASE WHEN Productnoinfo.Qty IS NULL THEN 1 ELSE Productnoinfo.Qty END)) AS ToScrapQty " & vbCrLf & _
                 "FROM Qualityrecord " & vbCrLf & _
                 "LEFT JOIN QualityRecordProductNoInfo ON Qualityrecord.Qualityrecordid = QualityRecordProductNoInfo.Qualityrecordid " & vbCrLf & _
                 "LEFT JOIN Productnoinfo ON Productnoinfo.Productnoinfoid = QualityRecordProductNoInfo.Productnoinfoid " & vbCrLf & _
                 "LEFT JOIN Qualitydisposeadvice ON Qualitydisposeadvice.Qualitydisposeadviceid = Productnoinfo.Qualitydisposeadviceid  " & vbCrLf & _
                 "WHERE Qualitydisposeadvicename = '报废' " & vbCrLf & _
                 "GROUP BY Qualityrecord.Qualityrecordid " & vbCrLf & _
                 ") temp2 ON temp2.Qualityrecordid = Qualityrecord.Qualityrecordid " & vbCrLf & _
                 "WHERE Qualityrecord.Recordstatus <> 4 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "'" & vbCrLf
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "'" & vbCrLf
            End If

            strSQL = strSQL & "ORDER BY Qualityrecord.Checkdate DESC" & vbCrLf

            Dim dtQualityRecord As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtQualityRecord = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtQualityRecord)

            strSQL = "SELECT Scrapinfo.Scrapinfoname, Container.Containername, Specbase.Specname, " & _
                "       Scrapinfo.Scrapqty, Employee.Fullname, Scrapinfo.Submitdate, Scrapinfo.Scrapinfoid " & _
                "FROM Scrapinfo " & _
                "INNER JOIN Container ON Scrapinfo.Containerid = Container.Containerid " & _
                "INNER JOIN Spec ON Scrapinfo.Specid = Spec.Specid " & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & _
                "INNER JOIN Employee ON Scrapinfo.Submitemployeeid = Employee.Employeeid " & vbCrLf & _
                "WHERE 1=1 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "'" & vbCrLf
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "'" & vbCrLf
            End If

            strSQL = strSQL & "ORDER BY Scrapinfo.Submitdate" & vbCrLf

            Dim dtScrapInfo As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtScrapInfo = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtScrapInfo)

            strSQL = "SELECT Speclastcheckhistory.Speclastcheckhistoryid " & vbCrLf & _
                "FROM Speclastcheckhistory" & vbCrLf & _
                "INNER JOIN Historymainline ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                "INNER JOIN Container ON Historymainline.Containerid = Container.Containerid " & _
                "INNER JOIN " & vbCrLf & _
                "(SELECT Workflowstep.Workflowstepid, Specbase.Specname" & vbCrLf & _
                "FROM Workflowstep" & vbCrLf & _
                "INNER JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "WHERE Workflowstep.Specbaseid = '0000000000000000'" & vbCrLf & _
                "UNION ALL" & vbCrLf & _
                "SELECT Workflowstep.Workflowstepid, Specbase.Specname" & vbCrLf & _
                "FROM Workflowstep" & vbCrLf & _
                "INNER JOIN Specbase ON Workflowstep.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "WHERE Workflowstep.Specid = '0000000000000000'" & vbCrLf & _
                ") CSpec" & vbCrLf & _
                "ON Historymainline.Workflowstepid = CSpec.Workflowstepid WHERE 1=1 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND  CSpec.Specname = '" & strSpecName & "'" & vbCrLf
            End If

            Dim dtSpecLastCheck As DataTable = dal.GetQualityRecordInfo(strSQL)

            strSQL = "SELECT Container.Containername, Specbase.Specname, ReturnSpecbase.Specname AS ReturnSpecname, Synergicinfo.Qty, Synergicinfo.Returnqty," & vbCrLf & _
                "       Employee.Fullname, Synergicinfo.Submitdate, Synergicinfo.Synergicinfoname, Synergicinfo.Returnnotes, " & vbCrLf & _
                "       (CASE WHEN Synergicinfo.Returnspecid IS NULL THEN '外协加工' ELSE '外协完成' END) AS SynergicStatus" & vbCrLf & _
                "FROM Synergicinfo " & vbCrLf & _
                "INNER JOIN " & vbCrLf & _
                "(SELECT MAX(Submitdate) AS Submitdate " & vbCrLf & _
                "FROM Synergicinfo GROUP BY Synergicinfo.Containerid, Synergicinfo.Specid) CMax " & vbCrLf & _
                "ON CMax.Submitdate = Synergicinfo.Submitdate" & vbCrLf & _
                "INNER JOIN Container ON Container.Containerid = Synergicinfo.Containerid" & vbCrLf & _
                "INNER JOIN Spec ON Synergicinfo.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "LEFT OUTER JOIN Spec ReturnSpec ON Synergicinfo.Returnspecid = ReturnSpec.Specid" & vbCrLf & _
                "LEFT OUTER JOIN Specbase ReturnSpecbase ON ReturnSpec.Specbaseid = ReturnSpecbase.Specbaseid" & vbCrLf & _
                "LEFT OUTER JOIN Employee ON Synergicinfo.Submitemployeeid = Employee.Employeeid WHERE 1=1 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND  ReturnSpecbase.Specname = '" & strSpecName & "'" & vbCrLf
            End If

            Dim dtSynergicinfo As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtSynergicinfo = DataAdjust.FormatDataSetSpecNameByTwoColumName("SpecName", "ReturnSpecname", dtSynergicinfo)

            Dim strSynergicinfoname As String = ""
            If dtSynergicinfo.Rows.Count > 0 Then
                strSynergicinfoname = dtSynergicinfo.Rows(0).Item("Synergicinfoname")
            End If
            QueryCondition.Add("Synergicinfoname", strSynergicinfoname)
            Dim dtSynergicSpecFactInfo As DataTable = GetSynergicSpecFactInfo(QueryCondition).DBTable.Copy

            Dim ds As New DataSet
            ds.Tables.Add(dtWorkRecord)
            ds.Tables(0).TableName = "dtWorkRecord"
            ds.Tables.Add(dtQualityRecord)
            ds.Tables(1).TableName = "dtQualityRecord"
            ds.Tables.Add(dtScrapInfo)
            ds.Tables(2).TableName = "dtScrapInfo"
            ds.Tables.Add(dtSpecLastCheck)
            ds.Tables(3).TableName = "dtSpecLastCheck"
            ds.Tables.Add(dtSynergicinfo)
            ds.Tables(4).TableName = "dtSynergicinfo"
            ds.Tables.Add(dtSynergicSpecFactInfo)
            ds.Tables(5).TableName = "dtSynergicSpecFactInfo"
            oResult.DBDataSet = ds
            oResult.Result = True

            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function


    ''' <summary>
    ''' 获取工序总检信息(多跟踪卡)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetSpecLastCheckInfoByContainers(ByVal dtContainerList As DataTable) As uMESProcessResult
        Dim oResult As New uMESProcessResult

        Try
            ''批次列表
            Dim strContainerList As String = ""
            For i As Integer = 0 To dtContainerList.Rows.Count - 1
                Dim strContainerName As String = dtContainerList.Rows(i)("ContainerName").ToString
                Dim strSpecName As String = dtContainerList.Rows(i)("SpecName").ToString
                If strContainerList = "" Then
                    strContainerList = "(c.ContainerName = '" & strContainerName & "' AND sb.specname = '" & strSpecName & "')"
                Else
                    strContainerList += " OR (c.ContainerName = '" & strContainerName & "' AND sb.specname = '" & strSpecName & "')"
                End If
            Next

            Dim strSQL As String = "SELECT c.Containername,c.Qty,pb.Productname,p.Productrevision,p.Description,sb.Specname," & vbCrLf & _
                    "   s.Specrevision,c.Qty,wrh.Qty AS ReportQty," & vbCrLf & _
                    "   (wrh.Qty-NVL(SUM(cch.Eligibilityqty),0)-NVL(SUM(cch.Nonsenseqty),0)) AS NeedCheckQty," & vbCrLf & _
                    "   NVL(SUM(cch.Eligibilityqty),0) AS EligibleQty,NVL(SUM(cch.Nonsenseqty),0) AS Nonsenseqty," & vbCrLf & _
                    "   e.Fullname,r.Resourcename,r.Resourcenum," & vbCrLf & _
                    "   t.TeamName,TO_CHAR(hml.TxnDate,'RRRR-MM-DD HH24:MI:SS') AS TxnDate,wrh.Workingreporthistoryid,wrh.Notes" & vbCrLf & _
                    "FROM Container c" & vbCrLf & _
                    "INNER JOIN Product p ON c.Productid = p.Productid" & vbCrLf & _
                    "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid" & vbCrLf & _
                    "INNER JOIN Currentstatus cu ON cu.Currentstatusid = c.Currentstatusid" & vbCrLf & _
                    "INNER JOIN Spec s ON cu.Specid = s.Specid" & vbCrLf & _
                    "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                    "INNER JOIN WorkCenterDispatching wcd ON wcd.Containerid = c.Containerid AND wcd.Specid = s.Specid" & vbCrLf & _
                    "INNER JOIN Workingreporthistory wrh ON wcd.Workcenterdispatchingid = wrh.Workcenterdispatchingid" & vbCrLf & _
                    "INNER JOIN Historymainline hml ON wrh.Historymainlineid = hml.Historymainlineid" & vbCrLf & _
                    "LEFT OUTER JOIN Conventioncheckhistory cch ON wrh.Workingreporthistoryid = cch.Workingreportid" & vbCrLf & _
                    "INNER JOIN Employee e ON hml.Employeeid = e.Employeeid" & vbCrLf & _
                    "LEFT OUTER JOIN Resourcedef r ON wrh.Resourceid = r.Resourceid" & vbCrLf & _
                    "LEFT OUTER JOIN Team t ON wrh.Teamid = t.Teamid" & vbCrLf & _
                    "WHERE c.Status = 1 AND c.Parentcontainerid IS NULL AND wrh.Qty > 0 AND hml.reversalstatus = 1" & vbCrLf

            If strContainerList <> "" Then
                strSQL += "AND (" & strContainerList & ")"
            End If

            strSQL += "GROUP BY c.Containername,pb.Productname,p.Productrevision,sb.Specname," & vbCrLf & _
            "   s.Specrevision,c.Qty,p.Description,wrh.Qty,e.Fullname,r.Resourcename,r.Resourcenum," & vbCrLf & _
            "   t.TeamName,hml.TxnDate,wrh.Workingreporthistoryid,wrh.Notes" & vbCrLf & _
            "ORDER BY c.Containername,sb.Specname,hml.TxnDate" & vbCrLf

            Dim dtWorkRecord As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtWorkRecord = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtWorkRecord)

            strSQL = "SELECT qr.Qualityrecordname,c.Containername,sb.Specname," & vbCrLf & _
                    "   s.Specrevision,e.Fullname,TO_CHAR(qr.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate,qr.Qty," & vbCrLf & _
                    "   DECODE(qr.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS Recordstatus," & vbCrLf & _
                    "   TechEmployee.Fullname AS TechnologistName,qr.Technologistdate,qr.Qualityrecordid," & vbCrLf & _
                    "   (CASE WHEN temp.NonScrapQty IS NULL THEN 0 ELSE temp.NonScrapQty END) AS NonScrapQty," & vbCrLf & _
                    "   (CASE WHEN temp2.ToScrapQty IS NULL THEN 0 ELSE temp2.ToScrapQty END) AS ToScrapQty" & vbCrLf & _
                    "FROM Qualityrecord qr" & vbCrLf & _
                    "INNER JOIN Container c ON qr.Containerid = c.Containerid" & vbCrLf & _
                    "INNER JOIN Spec s ON qr.Specid = s.Specid" & vbCrLf & _
                    "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                    "INNER JOIN Employee e ON e.Employeeid = qr.Checkemployeeid" & vbCrLf & _
                    "LEFT OUTER JOIN Employee TechEmployee ON TechEmployee.Employeeid = qr.Technologistemployeeid" & vbCrLf & _
                    "LEFT OUTER JOIN (" & vbCrLf & _
                    "   SELECT qr1.Qualityrecordid,SUM((CASE WHEN pi1.Qty IS NULL THEN 1 ELSE pi1.Qty END)) AS NonScrapQty" & vbCrLf & _
                    "   FROM Qualityrecord qr1" & vbCrLf & _
                    "   LEFT JOIN QualityRecordProductNoInfo qrpi1 ON qr1.Qualityrecordid = qrpi1.Qualityrecordid" & vbCrLf & _
                    "   LEFT JOIN Productnoinfo pi1 ON pi1.Productnoinfoid = qrpi1.Productnoinfoid" & vbCrLf & _
                    "   LEFT JOIN Qualitydisposeadvice qd1 ON qd1.Qualitydisposeadviceid = pi1.Qualitydisposeadviceid" & vbCrLf & _
                    "   WHERE qd1.Qualitydisposeadvicename <> '报废'" & vbCrLf & _
                    "   GROUP BY qr1.Qualityrecordid" & vbCrLf & _
                    ") temp ON temp.Qualityrecordid = qr.Qualityrecordid" & vbCrLf & _
                    "LEFT OUTER JOIN (" & vbCrLf & _
                    "   SELECT qr2.Qualityrecordid,SUM((CASE WHEN pi2.Qty IS NULL THEN 1 ELSE pi2.Qty END)) AS ToScrapQty" & vbCrLf & _
                    "   FROM Qualityrecord qr2" & vbCrLf & _
                    "   LEFT JOIN QualityRecordProductNoInfo qrpi2 ON qr2.Qualityrecordid = qrpi2.Qualityrecordid" & vbCrLf & _
                    "   LEFT JOIN Productnoinfo pi2 ON pi2.Productnoinfoid = qrpi2.Productnoinfoid" & vbCrLf & _
                    "   LEFT JOIN Qualitydisposeadvice qd2 ON qd2.Qualitydisposeadviceid = pi2.Qualitydisposeadviceid" & vbCrLf & _
                    "   WHERE qd2.Qualitydisposeadvicename = '报废'" & vbCrLf & _
                    "   GROUP BY qr2.Qualityrecordid" & vbCrLf & _
                    ") temp2 ON temp2.Qualityrecordid = qr.Qualityrecordid" & vbCrLf & _
                    "WHERE qr.Recordstatus <> 4" & vbCrLf

            If strContainerList <> "" Then
                strSQL += "AND (" & strContainerList & ")"
            End If

            strSQL = strSQL & "ORDER BY qr.Checkdate DESC" & vbCrLf

            Dim dtQualityRecord As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtQualityRecord = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtQualityRecord)

            strSQL = "SELECT sc.Scrapinfoname,c.Containername,sb.Specname," & vbCrLf & _
                    "   sc.Scrapqty,e.Fullname,sc.Submitdate,sc.Scrapinfoid" & vbCrLf & _
                    "FROM Scrapinfo sc" & vbCrLf & _
                    "INNER JOIN Container c ON sc.Containerid = c.Containerid" & vbCrLf & _
                    "INNER JOIN Spec s ON sc.Specid = s.Specid" & vbCrLf & _
                    "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                    "INNER JOIN Employee e ON sc.Submitemployeeid = e.Employeeid" & vbCrLf & _
                    "WHERE 1=1" & vbCrLf

            If strContainerList <> "" Then
                strSQL += "AND (" & strContainerList & ")"
            End If

            strSQL = strSQL & "ORDER BY sc.Submitdate" & vbCrLf

            Dim dtScrapInfo As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtScrapInfo = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtScrapInfo)

            strSQL = "SELECT slh.Speclastcheckhistoryid,c.ContainerName,sb.SpecName" & vbCrLf & _
                    "FROM Speclastcheckhistory slh" & vbCrLf & _
                    "INNER JOIN Historymainline hml ON slh.Historymainlineid = hml.Historymainlineid" & vbCrLf & _
                    "INNER JOIN Container c ON hml.Containerid = c.Containerid" & vbCrLf & _
                    "INNER JOIN (" & vbCrLf & _
                    "   SELECT wfs1.Workflowstepid,sb1.Specname" & vbCrLf & _
                    "   FROM Workflowstep wfs1" & vbCrLf & _
                    "   INNER JOIN Spec s1 ON wfs1.Specid = s1.Specid" & vbCrLf & _
                    "   INNER JOIN Specbase sb1 ON s1.Specbaseid = sb1.Specbaseid" & vbCrLf & _
                    "   WHERE wfs1.Specbaseid = '0000000000000000'" & vbCrLf & _
                    "   UNION ALL" & vbCrLf & _
                    "   SELECT wfs2.Workflowstepid,sb2.Specname" & vbCrLf & _
                    "   FROM Workflowstep wfs2" & vbCrLf & _
                    "   INNER JOIN Specbase sb2 ON wfs2.Specbaseid = sb2.Specbaseid" & vbCrLf & _
                    "   WHERE wfs2.Specid = '0000000000000000'" & vbCrLf & _
                    ") sb ON hml.Workflowstepid = sb.Workflowstepid" & vbCrLf & _
                    "WHERE 1 = 1 AND hml.reversalstatus = 1" & vbCrLf

            If strContainerList <> "" Then
                strSQL += "AND (" & strContainerList & ")"
            End If

            Dim dtSpecLastCheck As DataTable = dal.GetQualityRecordInfo(strSQL)

            strSQL = "SELECT c.Containername,sb.Specname,ReturnSpecbase.Specname AS ReturnSpecname," & vbCrLf & _
                    "   sy.Qty,sy.Returnqty,e.Fullname,sy.Submitdate,sy.Synergicinfoname,sy.Returnnotes," & vbCrLf & _
                    "   (CASE WHEN sy.Returnspecid IS NULL THEN '外协加工' ELSE '外协完成' END) AS SynergicStatus" & vbCrLf & _
                    "FROM Synergicinfo sy" & vbCrLf & _
                    "INNER JOIN (" & vbCrLf & _
                    "   SELECT MAX(Submitdate) AS Submitdate" & vbCrLf & _
                    "   FROM Synergicinfo sy1 GROUP BY sy1.Containerid,sy1.Specid" & vbCrLf & _
                    ") CMax ON CMax.Submitdate = sy.Submitdate" & vbCrLf & _
                    "INNER JOIN Container c ON c.Containerid = sy.Containerid" & vbCrLf & _
                    "INNER JOIN Spec s ON sy.Specid = s.Specid" & vbCrLf & _
                    "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                    "LEFT OUTER JOIN Spec ReturnSpec ON sy.Returnspecid = ReturnSpec.Specid" & vbCrLf & _
                    "LEFT OUTER JOIN Specbase ReturnSpecbase ON ReturnSpec.Specbaseid = ReturnSpecbase.Specbaseid" & vbCrLf & _
                    "LEFT OUTER JOIN Employee e ON sy.Submitemployeeid = e.Employeeid" & vbCrLf & _
                    "WHERE 1 = 1" & vbCrLf

            If strContainerList <> "" Then
                strSQL += "AND (" & strContainerList & ")"
            End If

            Dim dtSynergicinfo As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtSynergicinfo = DataAdjust.FormatDataSetSpecNameByTwoColumName("SpecName", "ReturnSpecname", dtSynergicinfo)

            Dim strSynergicinfoname As String = ""

            Dim dtSynergicSpecFactInfo As DataTable = GetSpecFactInfoBySynergicName(dtSynergicinfo).DBTable.Copy

            Dim ds As New DataSet
            ds.Tables.Add(dtWorkRecord)
            ds.Tables(0).TableName = "dtWorkRecord"
            ds.Tables.Add(dtQualityRecord)
            ds.Tables(1).TableName = "dtQualityRecord"
            ds.Tables.Add(dtScrapInfo)
            ds.Tables(2).TableName = "dtScrapInfo"
            ds.Tables.Add(dtSpecLastCheck)
            ds.Tables(3).TableName = "dtSpecLastCheck"
            ds.Tables.Add(dtSynergicinfo)
            ds.Tables(4).TableName = "dtSynergicinfo"
            ds.Tables.Add(dtSynergicSpecFactInfo)
            ds.Tables(5).TableName = "dtSynergicSpecFactInfo"
            oResult.DBDataSet = ds
            oResult.Result = True

            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 执行工序总检
    ''' </summary>
    ''' <param name="oRoutineCheck">执行工序总检所需参数</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RunSpecLastCheck(ByVal oRoutineCheck As uMESSpecLastCheckDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESCheckMainIBusiness.RunSpecLastCheck
        RunSpecLastCheck = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oRoutineCheck.ServerHostInfo

            Dim strTxnDocName, strTxnName As String
            strTxnDocName = "SpecLastCheckDoc"
            strTxnName = "SpecLastCheck"

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity


            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = oRoutineCheck.ContainerName
            dataEntity.ClientOtherValue = ""
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "QualifiedQty"
            dataEntity.ClientDataValue = oRoutineCheck.EligibilityQty
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "DefectQty"
            dataEntity.ClientDataValue = oRoutineCheck.DefectQty
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ScrapQty"
            dataEntity.ClientDataValue = oRoutineCheck.ScrapQty
            m_DataList.Add(dataEntity)

            If oRoutineCheck.CheckType = 2 Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
                dataEntity.ClientDataName = "FloorQty"
                dataEntity.ClientDataValue = oRoutineCheck.FloorQty
                m_DataList.Add(dataEntity)

            End If
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "CheckType"
            dataEntity.ClientDataValue = oRoutineCheck.CheckType
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Notes"
            dataEntity.ClientDataValue = oRoutineCheck.Notes
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ReportEmployee"
            dataEntity.ClientDataValue = oRoutineCheck.ReportEmployee
            m_DataList.Add(dataEntity)

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)

            success = clientData.RunTxnService(strTxnDocName, strTxnName, m_DataList, strInfo)

            If success = True Then
                RunSpecLastCheck = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 获取零件总检信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetProductLastCheckInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetProductLastCheckInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If
            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strSQL = "SELECT ContainerChild.Containername AS Productno, (CASE WHEN ContainerChild.Qty = 0 THEN '是' ELSE '否' END）AS IsLoss" & vbCrLf & _
                "FROM Container" & vbCrLf & _
                "INNER JOIN Container ContainerChild ON ContainerChild.Parentcontainerid = Container.Containerid" & vbCrLf & _
                "WHERE 1=1 "
            If strContainerName <> "" Then
                strSQL = strSQL & " AND Container.Containername = '" & strContainerName & "' "
            End If
            strSQL = strSQL & "ORDER BY ContainerChild.Containername"

            Dim dtWorkRecord As DataTable = dal.GetQualityRecordInfo(strSQL)

            If dtWorkRecord.Rows.Count > 0 Then
                dtWorkRecord.AcceptChanges()
                For i As Integer = 0 To dtWorkRecord.Rows.Count - 1
                    Dim str As String = dtWorkRecord.Rows(i)("Productno").ToString().Trim()
                    dtWorkRecord.Rows(i)("Productno") = str.Replace(strContainerName & "-", "")
                Next
            End If

            dtWorkRecord = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtWorkRecord)

            strSQL = "SELECT Qualityrecord.Qualityrecordname, Container.Containername, Specbase.Specname," & vbCrLf & _
                 "       Spec.Specrevision, Employee.Fullname, TO_CHAR(Qualityrecord.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate, Container.Qty, " & vbCrLf & _
                 "       DECODE(Qualityrecord.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS Recordstatus, " & vbCrLf & _
                 "       TechnologistEmployee.Fullname AS TechnologistName, Qualityrecord.Technologistdate,qualityrecord.revokenotes,qualityrecord.ProductCheckNotes, " & vbCrLf & _
                 "       (CASE WHEN temp.NonScrapQty IS NULL THEN 0 ELSE temp.NonScrapQty END) AS NonScrapQty, (CASE WHEN temp2.ToScrapQty IS NULL THEN 0 ELSE temp2.ToScrapQty END) AS ToScrapQty, Qualityrecord.Qualityrecordid " & vbCrLf & _
                 "FROM Qualityrecord" & vbCrLf & _
                 "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
                 "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
                 "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                 "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid " & vbCrLf & _
                 "LEFT OUTER JOIN Employee TechnologistEmployee ON TechnologistEmployee.Employeeid = Qualityrecord.Technologistemployeeid " & vbCrLf & _
                 "LEFT OUTER JOIN ( " & vbCrLf & _
                 "SELECT Qualityrecord.Qualityrecordid,SUM((CASE WHEN Productnoinfo.Qty IS NULL THEN 1 ELSE Productnoinfo.Qty END)) AS NonScrapQty " & vbCrLf & _
                 "FROM Qualityrecord " & vbCrLf & _
                 "LEFT JOIN QualityRecordProductNoInfo ON Qualityrecord.Qualityrecordid = QualityRecordProductNoInfo.Qualityrecordid " & vbCrLf & _
                 "LEFT JOIN Productnoinfo ON Productnoinfo.Productnoinfoid = QualityRecordProductNoInfo.Productnoinfoid " & vbCrLf & _
                 "LEFT JOIN Qualitydisposeadvice ON Qualitydisposeadvice.Qualitydisposeadviceid = Productnoinfo.Qualitydisposeadviceid  " & vbCrLf & _
                 "WHERE Qualitydisposeadvicename <> '报废' " & vbCrLf & _
                 "GROUP BY Qualityrecord.Qualityrecordid " & vbCrLf & _
                 ") temp ON temp.Qualityrecordid = Qualityrecord.Qualityrecordid " & vbCrLf & _
                 "LEFT OUTER JOIN ( " & vbCrLf & _
                 "SELECT Qualityrecord.Qualityrecordid,SUM((CASE WHEN Productnoinfo.Qty IS NULL THEN 1 ELSE Productnoinfo.Qty END)) AS ToScrapQty " & vbCrLf & _
                 "FROM Qualityrecord " & vbCrLf & _
                 "LEFT JOIN QualityRecordProductNoInfo ON Qualityrecord.Qualityrecordid = QualityRecordProductNoInfo.Qualityrecordid " & vbCrLf & _
                 "LEFT JOIN Productnoinfo ON Productnoinfo.Productnoinfoid = QualityRecordProductNoInfo.Productnoinfoid " & vbCrLf & _
                 "LEFT JOIN Qualitydisposeadvice ON Qualitydisposeadvice.Qualitydisposeadviceid = Productnoinfo.Qualitydisposeadviceid  " & vbCrLf & _
                 "WHERE Qualitydisposeadvicename = '报废' " & vbCrLf & _
                 "GROUP BY Qualityrecord.Qualityrecordid " & vbCrLf & _
                 ") temp2 ON temp2.Qualityrecordid = Qualityrecord.Qualityrecordid " & vbCrLf & _
                 "WHERE Qualityrecord.Recordstatus <> 4 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If

            strSQL = strSQL & "ORDER BY Qualityrecord.Checkdate DESC" & vbCrLf

            Dim dtQualityRecord As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtQualityRecord = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtQualityRecord)

            strSQL = "SELECT Scrapinfo.Scrapinfoname, Container.Containername, Specbase.Specname, " & _
                "       Scrapinfo.Scrapqty, Employee.Fullname, Scrapinfo.Submitdate, Scrapinfo.Scrapinfoid " & _
                "FROM Scrapinfo " & _
                "INNER JOIN Container ON Scrapinfo.Containerid = Container.Containerid " & _
                "INNER JOIN Spec ON Scrapinfo.Specid = Spec.Specid " & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & _
                "INNER JOIN Employee ON Scrapinfo.Submitemployeeid = Employee.Employeeid " & vbCrLf & _
                "WHERE 1=1 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If

            strSQL = strSQL & "ORDER BY Scrapinfo.Submitdate" & vbCrLf

            Dim dtScrapInfo As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtScrapInfo = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtScrapInfo)

            strSQL = "SELECT Speclastcheckhistory.Speclastcheckhistoryid " & vbCrLf & _
                "FROM Speclastcheckhistory" & vbCrLf & _
                "INNER JOIN Historymainline ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                "INNER JOIN Container ON Historymainline.Containerid = Container.Containerid " & _
                "INNER JOIN " & vbCrLf & _
                "(SELECT Workflowstep.Workflowstepid, Specbase.Specname" & vbCrLf & _
                "FROM Workflowstep" & vbCrLf & _
                "INNER JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "WHERE Workflowstep.Specbaseid = '0000000000000000'" & vbCrLf & _
                "UNION ALL" & vbCrLf & _
                "SELECT Workflowstep.Workflowstepid, Specbase.Specname" & vbCrLf & _
                "FROM Workflowstep" & vbCrLf & _
                "INNER JOIN Specbase ON Workflowstep.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "WHERE Workflowstep.Specid = '0000000000000000'" & vbCrLf & _
                ") CSpec" & vbCrLf & _
                "ON Historymainline.Workflowstepid = CSpec.Workflowstepid WHERE 1=1 AND Speclastcheckhistory.checkType = 2" & vbCrLf & _
                "AND historymainline.reversalstatus = 1" & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND  CSpec.Specname = '" & strSpecName & "'" & vbCrLf
            End If

            Dim dtSpecLastCheck As DataTable = dal.GetQualityRecordInfo(strSQL)

            Dim ds As New DataSet
            ds.Tables.Add(dtWorkRecord)
            ds.Tables(0).TableName = "dtWorkRecord"
            ds.Tables.Add(dtQualityRecord)
            ds.Tables(1).TableName = "dtQualityRecord"
            ds.Tables.Add(dtScrapInfo)
            ds.Tables(2).TableName = "dtScrapInfo"
            ds.Tables.Add(dtSpecLastCheck)
            ds.Tables(3).TableName = "dtSpecLastCheck"
            oResult.DBDataSet = ds
            oResult.Result = True

            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 批次移动
    ''' </summary>
    ''' <param name="oRoutineCheck">批次移动所需参数</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RunMoveIn(ByVal oRoutineCheck As uMESSpecLastCheckDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESCheckMainIBusiness.RunMoveIn
        RunMoveIn = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oRoutineCheck.ServerHostInfo

            Dim strTxnDocName, strTxnName As String
            strTxnDocName = "MoveInDoc"
            strTxnName = "MoveIn"

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = oRoutineCheck.ContainerName
            dataEntity.ClientOtherValue = ""
            m_DataList.Add(dataEntity)

            'dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "ReportEmployee"
            'dataEntity.ClientDataValue = oRoutineCheck.ReportEmployee
            'dataEntity.ClientOtherValue = ""
            'm_DataList.Add(dataEntity)

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)

            success = clientData.RunTxnService(strTxnDocName, strTxnName, m_DataList, strInfo)

            If success = True Then
                RunMoveIn = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 批次移动
    ''' </summary>
    ''' <param name="oRoutineCheck">批次移动所需参数</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RunMoveStd(ByVal oRoutineCheck As uMESSpecLastCheckDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESCheckMainIBusiness.RunMoveStd
        RunMoveStd = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oRoutineCheck.ServerHostInfo

            Dim strTxnDocName, strTxnName As String
            strTxnDocName = "MoveStdDoc"
            strTxnName = "MoveStd"

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = oRoutineCheck.ContainerName
            dataEntity.ClientOtherValue = ""
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity()
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ReportEmployee"
            dataEntity.ClientDataValue = oRoutineCheck.ReportEmployee
            dataEntity.ClientOtherValue = ""
            m_DataList.Add(dataEntity)

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)

            success = clientData.RunTxnService(strTxnDocName, strTxnName, m_DataList, strInfo)

            If success = True Then
                RunMoveStd = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 根据报工ID获取报工零件序号信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetWorkReportDetail(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetWorkReportDetail
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strWorkingreporthistoryid As String
            strWorkingreporthistoryid = ""
            If QueryCondition.Keys.Contains("Workingreporthistoryid") = True Then
                strWorkingreporthistoryid = QueryCondition("Workingreporthistoryid").ToString()
            End If

            ''============================================================================================
            strSQL = "SELECT WorkingReportHistoryChildConta.Childcontainer, DECODE(CheckPartNOList.Checkresulttype,'1','合格','2','不合格') AS Status" & vbCrLf & _
                    "FROM Conventioncheckhistory" & vbCrLf & _
                    "LEFT OUTER JOIN ConventionCheckHistoryCheckPar ON Conventioncheckhistory.conventioncheckhistoryid = ConventionCheckHistoryCheckPar.conventioncheckhistoryid" & vbCrLf & _
                    "LEFT OUTER JOIN CheckPartNOList ON CheckPartNOList.checkpartnolistid = ConventionCheckHistoryCheckPar.checkpartnolistid" & vbCrLf & _
                    "LEFT OUTER JOIN WorkingReportHistoryChildConta ON WorkingReportHistoryChildConta.workingreporthistoryid = Conventioncheckhistory.workingreportid" & vbCrLf & _
                    "AND CheckPartNOList.Partno = WorkingReportHistoryChildConta.Childcontainer" & vbCrLf & _
                    "WHERE WorkingReportHistoryChildConta.Workingreporthistoryid = '" & strWorkingreporthistoryid & "' " & vbCrLf
            strSQL = strSQL & "ORDER BY WorkingReportHistoryChildConta.Childcontainer"
            ''============================================================================================

            Dim strQuery As String = "SELECT wrhc.childcontainer FROM workingreporthistorychildconta wrhc" & vbCrLf & _
                                    "WHERE wrhc.workingreporthistoryid = '" & strWorkingreporthistoryid & "'" & vbCrLf & _
                                    "ORDER BY wrhc.childcontainer" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strQuery)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 根据转工单获取已经外协的工序信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetSynergicSpecFactInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESCheckMainIBusiness.GetSynergicSpecFactInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strSynergicinfoname As String
            strSynergicinfoname = ""
            If QueryCondition.Keys.Contains("Synergicinfoname") = True Then
                strSynergicinfoname = QueryCondition("Synergicinfoname").ToString()
            End If

            strSQL = "SELECT Synergicinfo.Synergicinfoname, Specbase.Specname, Spec.Specrevision, Synergicspecinfo.Specindex, " & vbCrLf & _
                    "       ReturnSpecbase.Specname AS ReturnSpecname, ReturnSpec.Specrevision AS ReturnSpecrevision" & vbCrLf & _
                    "FROM Synergicinfo" & vbCrLf & _
                    "INNER JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid" & vbCrLf & _
                    "INNER JOIN Synergicspecinfo ON SynergicInfoSynergicSpecInfo.Synergicspecinfoid = Synergicspecinfo.Synergicspecinfoid" & vbCrLf & _
                    "INNER JOIN Spec ON Synergicspecinfo.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                    "INNER JOIN Spec ReturnSpec ON Synergicinfo.Returnspecid = ReturnSpec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ReturnSpecbase ON ReturnSpec.Specbaseid = ReturnSpecbase.Specbaseid" & vbCrLf & _
                    "WHERE Synergicinfo.Synergicinfoname = '" & strSynergicinfoname & "' " & vbCrLf
            strSQL = strSQL & "ORDER BY Synergicinfo.Synergicinfoname,Synergicspecinfo.Specindex "

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 根据转工单获取已经外协的工序信息(多个转工单)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetSpecFactInfoBySynergicName(ByVal dtSynergicName As DataTable) As uMESProcessResult
        Dim oResult As New uMESProcessResult

        Try
            Dim strSynergicNameList As String = ""
            For i As Integer = 0 To dtSynergicName.Rows.Count - 1
                Dim strSynergicName As String = dtSynergicName.Rows(i)("SynergicName").ToString
                If strSynergicNameList = "" Then
                    strSynergicNameList = "'" & strSynergicName & "'"
                Else
                    strSynergicNameList += ",'" & strSynergicName & "'"
                End If
            Next

            Dim strSQL As String = "SELECT sy.Synergicinfoname,sb.Specname,s.Specrevision,ssi.Specindex," & vbCrLf & _
                                    "   ReturnSpecbase.Specname AS ReturnSpecname,ReturnSpec.Specrevision AS ReturnSpecrevision" & vbCrLf & _
                                    "FROM Synergicinfo sy" & vbCrLf & _
                                    "INNER JOIN SynergicInfoSynergicSpecInfo sysi ON sysi.Synergicinfoid = sy.Synergicinfoid" & vbCrLf & _
                                    "INNER JOIN Synergicspecinfo ssi ON sysi.Synergicspecinfoid = ssi.Synergicspecinfoid" & vbCrLf & _
                                    "INNER JOIN Spec s ON ssi.Specid = s.Specid" & vbCrLf & _
                                    "INNER JOIN Specbase sb ON s.Specbaseid = sb.Specbaseid" & vbCrLf & _
                                    "INNER JOIN Spec ReturnSpec ON sy.Returnspecid = ReturnSpec.Specid" & vbCrLf & _
                                    "INNER JOIN Specbase ReturnSpecbase ON ReturnSpec.Specbaseid = ReturnSpecbase.Specbaseid" & vbCrLf & _
                                    "WHERE 1 = 1" & vbCrLf
            If strSynergicNameList <> "" Then
                strSQL += "AND sy.Synergicinfoname IN (" & strSynergicNameList & ")" & vbCrLf
            End If

            strSQL += "ORDER BY sy.Synergicinfoname,ssi.Specindex" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function


#Region "获取历史报工记录"
    Public Function GetReportHistory(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uMESCheckMainIBusiness.GetReportHistory
        'Dim oResult As New uMESProcessResult

        Dim strContainerName As String = ""
        If QueryCondition.Keys.Contains("ContainerName") = True Then
            strContainerName = QueryCondition("ContainerName").ToString()
        End If
        Dim strSpecName As String = ""
        If QueryCondition.Keys.Contains("SpecName") = True Then
            strSpecName = QueryCondition("SpecName").ToString()
        End If

        Dim strSQL As String = "SELECT c.containername,t.eligibilityqty,t.nonsenseqty,h.workflowstepid,s.specid,sb.specname,sb1.specname,t.conventioncheckhistoryid  FROM conventioncheckhistory t " & vbCrLf & _
                               "LEFT JOIN historymainline h ON h.historymainlineid=t.historymainlineid " & vbCrLf & _
                               "LEFT JOIN container c ON c.containerid=h.containerid " & vbCrLf & _
                               "LEFT JOIN workflowstep w ON w.workflowstepid=h.workflowstepid " & vbCrLf & _
                               "LEFT JOIN specbase sb ON sb.specbaseid=w.specbaseid " & vbCrLf & _
                               "LEFT JOIN spec s ON s.specid=w.specid " & vbCrLf & _
                               "LEFT JOIN specbase sb1 ON sb1.specbaseid=s.specbaseid " & vbCrLf & _
                               "WHERE h.reversalstatus = 1 AND (sb.specname='" & strSpecName & "' OR  sb1.specname='" & strSpecName & "') AND c.containername in (" & strContainerName & ")"
        Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
        Return dtMain
    End Function
#End Region

#Region "判断sequence是否最大"
    Public Function IsLastSequence(ByVal ContainerName As String) As Boolean Implements uMESCheckMainIBusiness.IsLastSequence
        Dim Bool As Boolean = False
        Dim strSql As String = "SELECT wf.sequence from Container c " & vbCrLf & _
                               "LEFT JOIN Currentstatus cu ON cu.currentstatusid=c.currentstatusid " & vbCrLf & _
                               "LEFT JOIN Workflowstep wf ON cu.workflowstepid=wf.workflowstepid " & vbCrLf & _
                               "WHERE c.containername='" & ContainerName & "' " & vbCrLf & _
                               "AND  wf.islaststep=1"
        '"AND wf.sequence= ( SELECT  MAX(sequence) FROM workflowstep ) "
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        If DT.Rows.Count <> 0 Then
            Bool = True
        End If
        Return Bool
    End Function
#End Region

#Region "得到批次数量"
    Public Function GetContianerNumber(ByVal QueryCondition As Dictionary(Of String, String)) As Integer Implements uMESCheckMainIBusiness.GetContianerNumber

        Dim strContainerName As String = ""
        If QueryCondition.Keys.Contains("ContainerName") = True Then
            strContainerName = QueryCondition("ContainerName").ToString()
        End If

        Dim strSql As String = "SELECT c.qty  FROM container c WHERE c.containername in (" & strContainerName & ")"
        Dim tmpTable As DataTable = dal.StandardGetDataTable(strSql)

        If tmpTable.Rows.Count > 0 Then
            Return Convert.ToInt32(tmpTable.Rows(0)("qty"))
        Else
            Return 0
        End If
    End Function
#End Region

#Region "报工是否已检验"
    Public Function WorkingReportIsCheck(ByVal Workreportid As String) As Boolean Implements uMESCheckMainIBusiness.WorkingReportIsCheck
        Dim Bool As Boolean = False
        Dim strSql As String = String.Format("select t.Conventioncheckhistoryid from Conventioncheckhistory t where t.workingreportid='{0}'", Workreportid)
        LogHelp.WriteLog("检查报工单是否检验：" + strSql)
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        If DT.Rows.Count > 0 Then
            Bool = True
            LogHelp.WriteLog(Workreportid + "已经检验")
        End If
        Return Bool
    End Function
#End Region

#Region "批次当前工序是否已派工"
    Public Function ContainerCurrentSpecIsDispatch(ByVal containername As String) As Boolean Implements uMESCheckMainIBusiness.ContainerCurrentSpecIsDispatch
        Dim result = False
        Dim strSQL As String = String.Format("select w.workcenterdispatchingid from workcenterdispatching w " +
                                             "left join condispatch cd on cd.containerid=w.containerid and (cd.revofrcdid = w.specid or cd.specid=w.specid)" +
                                             "where cd.containername='{0}'", containername)
        ' LogHelp.WriteLog("批次当前工序是否已派工：" + strSQL)
        Dim DT As DataTable = dal.StandardGetDataTable(strSQL)
        If DT.Rows.Count > 0 Then
            result = True
            LogHelp.WriteLog(containername + "批次当前工序已派工")
        End If
        Return result
    End Function
#End Region

End Class

